Help

Unexpected results with simple IF formula

Topic Labels: Formulas
Solved
Jump to Solution
355 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Gunne1
6 - Interface Innovator
6 - Interface Innovator

I have two Date fields. I need a third field that simply displays the date in the first field unless it's blank, then displays the date in the second field. Here is the formula I'm using:

 

IF({Dev End} = '', {Deadline}, {Dev End})

 

The formatting options for both Date fields and the Formula field are the same:

Screenshot 2023-07-12 at 1.30.06 PM.png

I'm having two problems. First, the output date in this new Formula field is coming back one day before where it should be. I assume I could just add a day to the result within the formula, but I'm not sure why this happens in the first place. I suspect they have to do with the Time Zone. If I display the time zone, I see GMT, but I can't seem to select my time zone (EDT) unless I also display the time. I'm not interested in the time.

Screenshot 2023-07-12 at 1.28.52 PM.png

The second problem is that I'm not able to use the Formula field as the end date in a Gantt view. I received the following error:

Screenshot 2023-07-12 at 1.33.21 PM.png

 

I assume this may also be because of the time zones.

Can I globally set the base to use EDT rather than GMT? Or is the fix here to turn "display time" on all of my Date fields and manually set them to my actual time zone instead of the default, GMT?

 

1 Solution

Accepted Solutions
Jonathan_Gunne1
6 - Interface Innovator
6 - Interface Innovator

I think I solved this... For the Formula field, I turned on "use the same time zone..." and set it to GMT. This is easier than manually setting all of my Date fields to my actual time zone. It seems like it would be nice to set a default time zone for an entire base rather than each Date field defaulting to GMT regardless of your location.

Screenshot 2023-07-12 at 2.02.18 PM.png

 

See Solution in Thread

1 Reply 1
Jonathan_Gunne1
6 - Interface Innovator
6 - Interface Innovator

I think I solved this... For the Formula field, I turned on "use the same time zone..." and set it to GMT. This is easier than manually setting all of my Date fields to my actual time zone. It seems like it would be nice to set a default time zone for an entire base rather than each Date field defaulting to GMT regardless of your location.

Screenshot 2023-07-12 at 2.02.18 PM.png