Incorporating SET_TIMEZONE into a formula

[Robyn_Cowie]

Hi everyone,

I am brand new at spread sheets and formulas and just trying to create a simple database to make my daily workflow more efficient. At the moment I am struggling with the time zone nuances in airtable.

I think I have worked out the formula I need but it is not showing the correct result consistently. None of my date/time fields are set to use GMT, they are all local. I have used the SET_TIMEZONE function successfully with simple DATETIME_FORMAT formulas in other fields but not sure how to apply it here.

I am a health care provider who cares for pregnant women. I would like to be able to display my clients gestational age (how pregnant they are) in a column if they are pregnant. If they have given birth I would like the same column to show how many hours out the baby is instead.

I have an “EDD” field which is their expected due date (pregnancy is calculated as 40 weeks). “Delivery Date” is the day and time they actually gave birth. I want the gestational age to be displayed as weeks+days example: 38+4 (38 weeks and 4 days pregnant)

So far I have:

IF({Delivery Date}>"", (DATETIME_DIFF(TODAY(),{Delivery Date}, ‘days’)),(FLOOR(((280-DATETIME_DIFF({EDD},TODAY(), ‘days’))/7))&"+"&((280-DATETIME_DIFF({EDD},TODAY(),‘days’))
-((FLOOR(((280-DATETIME_DIFF({EDD},TODAY(),‘days’))/7)))*7))))

It seems to work except for the time zone issue. If someone could help me to incorporate the SET_TIMEZONE function I would be really grateful!

[W_Vann_Hall]

I still batter my brain over Airtable’s date-handling. I’ve tried several times to write a cheat-sheet about what to do when, but I always get so lost while trying to set up all the possible variations I have to quit.

That said, I think what you need is given in the second numbered item in this reply. (I’m not positive, though, because it’s after midnight here, and the last thing I want to do is fall down a four-hour rabbit hole from which I’ll no doubt emerge even more confused. :winking_face: ) Take a look and see if the code presented there — most likely the simplified snippet listed second — can be merged with yours to give you the answer you want. If not, let me know, and post-sleep and -coffee I’ll give it a whack…