If blank = Time of Day Field + 1 Hour

Question

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.&nbsp;
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------------------------------

chayceduncan · Answer

The formula you have is a good start - something like:&nbsp;
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 · Answer

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)
&nbsp; &nbsp; &nbsp; &nbsp;-- ToTimestamp ([WO Date], [Appt. Time]) --
"Appt. TimeStamp End Date/Time" - (Formula Date/Time)
&nbsp; &nbsp; &nbsp; -- 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 · Answer

Just so I can make sure I still understand - you created future records that have a start date &amp; 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?&nbsp;

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:&nbsp;
Appt. TimeStamp End Date/Time

If( isnull([Appointment Estimated End Time]), 
&nbsp; &nbsp; &nbsp; [Appt. TimeStamp Start Date/Time] + Hours (1),&nbsp;
ToTimestamp ([WO Date], [Est. End Time]))------------------------------Chayce Duncan------------------------------

Forum Discussion

If blank = Time of Day Field + 1 Hour