📅 Guide: Mastering Dates & Times

The Date & Time column contains both a date and time value within one single value. Dates and times can therefore be tricky to understand and to work with. It helps to keep things simple to stay out of trouble.

Here are a few tips:

Writing dates
Avoid writing dates manually. Use a picker instead. If done via import, be weary of the format of the date column in the source.

Relations
Anytime you create a relation with a date, convert the date to either a string (template column) or a number (math column). This will avoid issues with different date formats from device to device.

How to convert a raw date to an number in YYYYMMDD format
In a Math column: Year(date)*10^4+Month(date)*10^2+Day(date)

Date vs Date-Time
In the Date & Time column, the configuration to “date only” or “time only” affects what is displayed only. Under the hood in the data editor, both the date and time are stored.

On or After // On or Before // After // Before
These four options are used in conditions for visibility, filters, and if-then-else columns when used with a date.
Caution:
• On or After // On or Before: Applies to date only. Ignores the time (because of ‘on’).
• After // Before: Applies to both date and time. Takes the time into account.

Change the format of a date
Before changing the format of a date, change the date-time value so the time element is removed and only the date element remains. Using the Format Date column directly on raw date-time values is not recommended, as it often leads to issues. Instead:

  1. Convert the raw date to an integer in YYYYMMDD format using a Math column (see above)
  2. Create a new date from this integer with a ‘Text to Date’ computed column.
  3. Use this new date in a ‘Format Date’ computed column.

The initial 2 steps ensure you’re working with an unambiguous date and a date only rather than date and time. ‘Format Date’ then becomes more reliable.

For more information about dates, see Glide Docs.


Date formulas

In the following formulas, ‘date’ can be replaced by the ‘Now’ value. Use these in a math computed column.

Last day of the previous month

date-DAY(date)

First day of the month

date-DAY(date)+1

Last day of the month

(date-DAY(date)+45)-DAY(date-DAY(date)+45)

First day of any year

((now+CEILING((Year-YEAR(now))*365.2424)-DAY(now)+15)-(MONTH(now)-1)*30)-DAY((now+CEILING((Year-YEAR(now))*365.2424)-DAY(now)+15)-(MONTH(now)-1)*30)+1

‘Year’ is the year whose first day is being determined, while YEAR is the function YEAR().

Last day of any year

((now+CEILING((Year-YEAR(now)+1)*365.2424)-DAY(now)+15)-(MONTH(now)-1)*30)-DAY((now+CEILING((Year-YEAR(now)+1)*365.2424)-DAY(now)+15)-(MONTH(now)-1)*30)

‘Year’ is the year whose first day is being determined, while YEAR is the function YEAR().

Add m months to a date, adding the exact number of days

If the arrival month doesn’t have enough days, the output will be a date in the following month.

((date-DAY(date)+15)+TRUNC(months/12*365.2424))-DAY((date-DAY(date)+15)+TRUNC(months/12*365.2424))+DAY(date)

‘months’ is the number of months to be added.

Add m months to a date and retain the same day number

Retain the same day number unless a month doesn’t have enough days. If the arrival month doesn’t have enough days, the output will be a date in the following month.

((date-DAY(date)+15)+TRUNC(months/12*365.2424))-DAY((date-DAY(date)+15)+TRUNC(months/12*365.2424))+DAY(date)-MOD((DAY(((date-DAY(date)+15)+TRUNC(months/12*365.2424))-DAY((date-DAY(date)+15)+TRUNC(months/12*365.2424))+DAY(date))-DAY(date)),DAY(date))

‘months’ is the number of months to be added.

Add n business days to a date (exclude weekends)

Add n business days to a specified date assuming Saturday and Sunday are weekends. This formula will always return a business day and it doesn’t account for public holidays.

date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400+2*(max(weekday(date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400)-6,0))+(max(weekday(date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400)-2*weekday(date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400)+2,0))

This post is a compilation of the contributions from @Jeff_Hager, @Darren_Murphy, @tuzin, @mattbrowning, @Oscar_V88, @Robert_Petitto, @ThinhDinh and many others in the forum.

11 Likes

Thank you for combining all this valuable info into one post!

This is also relevant here

This math formula adds N business days to a specified date assuming Saturday and Sunday are weekends. This formula will always return a business day and it doesn’t account for public holidays.

date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400
+
2*(max(weekday(date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400)-6,0))
+
(max(weekday(date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400)-2*weekday(date+floor(n/5)*7+mod(n,5)-hour(date)/24-minute(date)/1440-second(date)/86400)+2,0))
6 Likes

Do you have an explanation regarding ‘now’? Is there a ‘now’ for the server?

‘Now’ is just a variable that can be replaced with with current time (Now) in a math column. The replacement value can be named anything you want, such as ‘Date’ or ‘X’, but ‘Now’ may make more sense based on the math you are trying to perform.

Hi Jeff,
Can you provide more details about ‘Now’ concerning server time? How does the calculation work in this context?

It’s not server time. It’s the current time on the device. It’s like using a date/time picker and selecting the current date and time…except you don’t have to pick it, and it updates automatically every 10 seconds.

Try it in a math column like I showed and the result will be the current date and time and will continuously update.

If based on user devices, does this avoid the time being tampered with by users?

No. A user could still alter their device time, which could affect the math result.

I’m using Google server for now. Given the increasingly complex updates from Glide, it piqued my curiosity about the Glide approach."

Yeah, usually it’s not a concern, but for now you would have to rely on an external service if you reliably wanted to work with a server based time.

1 Like

Jeff, I have an idea, could it be possible through this hybrid approach? Taking the example when the user first enters the form, here the value ‘now-google server’ is fetched only once, and there is no need to consider the user’s current time until they complete the form.

Yeah, that may work.

This is not easy, do you have any ideas?
I think I need to take a nap for now.

I don’t know how you would be getting the server time now, so would probably need to know that first, along with a description of what you think the whole process would look like. In all honesty, I’m not completely following along with your idea.

My approach is to simply fetch “now” from the spreadsheet. This time is accurate. This time needs to be written to the “usc” column, where a countdown calculation against the predefined deadline needs to be performed, without worrying about Glide’s sync duration.

Where is the column that would fetch the time? I think I very faintly remember your situation from before. I would think you could have the fetched time loaded in the table that is the parent table of the screen that contains the form button, and then pass the time through the form as a Value component. Or you could have an action sequence before or after the form is opened to write the time to a USC column.

If uncertainty looms in the shadows, I will give it a try.

1 Like

If he fetches “now” from the spreadsheet, would there be a significant delay? How often does that sync?

1 Like

I don’t think that would be reliable enough. I don’t know the exact numbers, but I always assume up to 3 minutes to sync with an external data source.

And then it depends which source. Google Sheets seems quick, Airtable less so.

2 Likes

I’m not sure. I was hoping for more clarification on that process. If it were me, I’d probably try to find a public time API that I can call on demand and get the current time. I think it’s all possible, but depends on the flow. You’re both correct though, relying on the normal sync between Glide and Google wouldn’t be sufficient. An API call would be required.

2 Likes