Forum Discussion

JillianP's avatar
Qrew Member
4 months ago

If blank = Time of Day Field + 1 Hour

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

3 Replies

  • 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
    • JillianP's avatar
      Qrew Member

      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
      • ChayceDuncan's avatar
        Qrew Captain

        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