Invalid Date?

[samb1758]

I am trying to make a formula that takes the date/time from a previous column, and if the time is in the p.m, display the current date, however, if the time is in the a.m, to display the previous day's date. Attached it my current formula, which works for the p.m. times, but shows a mixture of 'Invalid Date' and an oddly specific time (see photos)

Any ideas what I'm doing wrong?

[TheTimeSavingCo · Accepted solution]

Hm could you try switching stuff around in your last line so that we're doing the DATETIME_FORMAT on the calculated date?  Something like this:  (Not 100% on the syntax)

DATETIME_FORMAT(
  DATEADD(
    {Start Time}, 
    -1, 
    'days'
  ), 
  'DD/MM/YYYY'
)

[samb1758]

That seems to have fixed it! thank you so much!

[TheTimeSavingCo]

Glad I could help!

[kuovonne]

I know that you have already marked a solution, but I think that your solution is incomplete. 

Your solution does not take timezones into account. When checking to see if the time is AM or PM, you are not specifying a timezone. By default, Airtable uses the GMT timezone. If you are in GMT, leaving out the timezone will work, but if you are anywhere else, you will get incorrect results for times when your AM/PM do not match GMT AM/PM. 

Your original formula returned a combination of either a date text string or a date object. As a result, the entire field result was forced into a text string. That explains why you got the strange looking dates. The revised formula converts everything into text strings, which look pretty, but may not be the result you want. For example, the text dates will not show up on a calendar properly and may have unpredictable results if you try to do additional date math with them. 

[TheTimeSavingCo]

Ouch yeah, you're right kuovonne.  I put something together so that I could visualize it better:

The field "With timezone" has the formula

DATETIME_FORMAT(
  SET_TIMEZONE(
    {Start Time}, 
    'Asia/Singapore'
  ), 
  'A'
)

And the formula for "Without timezone" is

DATETIME_FORMAT({Start Time}, 'A')

---

I was thinking that as a potential solution perhaps we could use the "Use the same time zone" setting?

Once we start using that setting the times seem to output the way we would want it to I think

And here's a link to the base