Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Invalid Date?

Topic Labels: Base design Formulas
Solved
Jump to Solution
123 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

 

 

1 Solution

Accepted Solutions

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'
)

 

See Solution in Thread

5 Replies 5

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'
)

 

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

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. 

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

Screenshot 2023-01-24 at 12.41.21 PM.png

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?

Screenshot 2023-01-24 at 12.43.22 PM.png

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

Screenshot 2023-01-24 at 12.44.32 PM.png
And here's a link to the base