Discussions

 View Only
  • 1.  If blank = Time of Day Field + 1 Hour

    Posted 23 days ago

    I followed another thread and was successfully able to create time blocks in a calendar report. However, because I created a new field for moving forward, I have thousands of records without any data in that field. 

    I am trying to create a formula that states if the field is empty then add an hour from another time of day field.

    I had [Appointment Start Time] already and have created [Appointment Estimated End Time] (Both Formula - Time of Day fields)

    In the calendar, the calendar is based on a Timestamp field of [Work Order Date] + [Appointment Start Time] (Formula - Date/Time field)

    For the Calendar's "End Date" I will also use a Timestamp field of [Work Order Date] + [Appointment Estimated End Time] (Formula - Date/Time field)

    It will work - but brings me to the issue that I want to enter data into the empty fields of [Appointment Estimated End Time] so that all previous records still show within the calendar.

    I know this is wrong but I want it to read something like:

    IF (is null ([Appointment Estimated End Time]), ([Appointment Start Time] + Hours (1))

    In the field I want it to be an hour after the start time. It needs to read "if blank" rather than having it go to a default 1 hour. It won't always be 1 hour moving forward and another time of day will be manually entered.



    ------------------------------
    Jillian P
    ------------------------------


  • 2.  RE: If blank = Time of Day Field + 1 Hour

    Posted 23 days ago

    The formula you have is a good start - something like: 

    IF ( isnull([Appointment Estimated End Time]), [Appointment Start Time] + Hours (1), [Appointment Estimated End Time])

    where it will add 1 hour to start if there is no end time, else use the end time.



    ------------------------------
    Chayce Duncan
    ------------------------------



  • 3.  RE: If blank = Time of Day Field + 1 Hour

    Posted 20 days ago

    I'm not having any luck. The field "Est. End Time" isn't identified within the formula field of "Est. End Time". Date / Time and Time of Day fields don't seem to like being in the same formula?

    My fields are as follows:

    "WO Date" - (Date)

    "Appt. Time" - (Time of Day)

    "Est. End Time" - (Time of Day)

    "Appt. TimeStamp Start Date/Time" - (Formula Date/Time)

           -- ToTimestamp ([WO Date], [Appt. Time]) --

    "Appt. TimeStamp End Date/Time" - (Formula Date/Time)

          -- ToTimestamp ([WO Date], [Est. End Time]) --

    Which field would your formula go under?

    "Est. End Time" does not want to reference itself within a formula.



    ------------------------------
    Jillian P
    ------------------------------



  • 4.  RE: If blank = Time of Day Field + 1 Hour

    Posted 20 days ago

    Just so I can make sure I still understand - you created future records that have a start date & start time but they don't have a end date / end time so you're trying to simply add an hour to start time so it appears on reports correct? 

    If I'm close - then the formula you should be updating is the appt. timestamp end date/time with some version of what I provided but now updated since you provided your field names: 

    Appt. TimeStamp End Date/Time

    If( isnull([Appointment Estimated End Time]),

          [Appt. TimeStamp Start Date/Time] + Hours (1), 

    ToTimestamp ([WO Date], [Est. End Time]))



    ------------------------------
    Chayce Duncan
    ------------------------------