# 📅 Guide on Dates & Times

**URL:** <https://community.glideapps.com/t/guide-on-dates-times/70381>\
**Category:** Community Resources\
**Created:** [February 1, 2024, 6:37pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381 "2024-02-01T18:37:32Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\
**Post date:** [February 1, 2024, 6:37pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/1 "2024-02-01T18:37:32Z")

</div>

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](https://www.glideapps.com/docs).

* * *

**Date formulas**

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

> **Date at midnight 00:00**
>
> ```auto
> date-(ABS(date)-TRUNC(date))+0.0000000001
> 
> Note: This formula is experimental and not guaranteed to be accurate. This formula returns the date at 00:00 *plus a fraction of second* to be certain the date returned is just passed midnight.
> 
> ```

> **Last day of the previous month**
>
> ```auto
> date-DAY(date)
> 
> Note: The date element of the date-time value is now the last day of the previous month, but the time element of this date-time value remains the same as the original date.
> 
> ```

> **First day of the month**
>
> ```auto
> date-DAY(date)+1
> 
> ```

> **First day of the month at midnight 00:00**
>
> ```auto
> date
> -DAY(date)+1
> -HOUR(date)/24
> -MINUTE(date)/1440
> -SECOND(date)/86400
> 
> Or experimental:
> 
> (date
> -DAY(date)+1)-(ABS(date
> -DAY(date)+1)-TRUNC(date
> -DAY(date)+1))+0.0000000001
> 
> ```

> **Last day of the month**
>
> ```auto
> (date-DAY(date)+45)-DAY(date-DAY(date)+45)
> 
> ```

> **First day of any year**
>
> ```auto
> ((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**
>
> ```auto
> ((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.
> 
> ```auto
> ((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~~ the last day of the month.
> 
> ```auto
> ((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 m months to a date, specifically in the case of a contract such as an apartment rental where the end date is the last day of the month**
>
> ```auto
> (((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))-
> DAY((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))+
> DAY(date)-MOD((DAY(((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))-
> DAY((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))+DAY(date))-
> DAY(date)),DAY(date)))
> -
> DAY(((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))-
> DAY((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))+
> DAY(date)-MOD((DAY(((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))-
> DAY((date-DAY(date)+15)+TRUNC((months+1)/12*365.2424))+DAY(date))-
> DAY(date)),DAY(date)))
> 
> ```

> **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.
> 
> ```auto
> 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))
> 
> ```

> **Get Ordinal day (day of the year)**
>
> This will return a day number (1-366) for a given date.
> 
> ```auto
> Round(Date-
> (((Date+CEILING((YEAR(Date)-YEAR(Date))*365.2424)-DAY(Date)+15)-(MONTH(Date)-1)*30)-DAY((Date+CEILING((YEAR(Date)-YEAR(Date))*365.2424)-DAY(Date)+15)-(MONTH(Date)-1)*30)+1))
> 
> ```

> **Get last day of a specific month following a given date**
>
> For example: Assuming you want the March 31st following a given day(which could be in the current year or the next year…If the user chooses the date 4th May 2025, the resulting date should be 31st March 2026. If the user chooses the date 1st Feb 2024, the resulting date should be 31st March 2024.
> 
> ```auto
> ((Date-DAY(Date)+15)+TRUNC( 
> (3-MONTH(Date)+1+
> 12*MAX(0,SIGN(3-MONTH(Date))*-1)) 
> /12*365.2424))
> -
> DAY((Date-DAY(Date)+15)+TRUNC( 
> (3-MONTH(Date)+1+
> 12*MAX(0,SIGN(3-MONTH(Date))*-1)) 
> /12*365.2424))
> 
> ```
> 
> To choose a different month other than March, change the four 3’s to the month number you want.

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.

---

<div class="post-metadata">

**Author:** ![tuzin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tuzin/32/86077_2.png) [@tuzin](https://community.glideapps.com/u/tuzin)\
**Post date:** [February 1, 2024, 6:43pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/2 "2024-02-01T18:43:57Z")

</div>

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.

```auto
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))

```

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 7:51pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/3 "2024-02-01T19:51:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 8:28pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/4 "2024-02-01T20:28:19Z")

</div>

‘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.

 ![Screenshot_20240201-142517](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/c/6c8f052732a91a9db355ec23cee6c6e5971318ba.png)

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 8:36pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/5 "2024-02-01T20:36:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 8:40pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/6 "2024-02-01T20:40:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 8:48pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/7 "2024-02-01T20:48:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 8:49pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/8 "2024-02-01T20:49:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 8:56pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/9 "2024-02-01T20:56:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 9:04pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/10 "2024-02-01T21:04:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 9:19pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/11 "2024-02-01T21:19:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 9:30pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/12 "2024-02-01T21:30:47Z")

</div>

Yeah, that may work.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 9:41pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/13 "2024-02-01T21:41:38Z")

</div>

> [@Himaladin](#):
>
> no need to consider the user’s current time

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 9:50pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/14 "2024-02-01T21:50:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 9:59pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/15 "2024-02-01T21:59:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 10:06pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/16 "2024-02-01T22:06:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [February 1, 2024, 10:22pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/17 "2024-02-01T22:22:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [February 1, 2024, 11:34pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/18 "2024-02-01T23:34:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\
**Post date:** [February 1, 2024, 11:37pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/19 "2024-02-01T23:37:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 1, 2024, 11:47pm UTC](https://community.glideapps.com/t/guide-on-dates-times/70381/20 "2024-02-01T23:47:51Z")

</div>

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.

[Next page](https://community.glideapps.com/t/guide-on-dates-times/70381.md?page=2)