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:
- Convert the raw date to an integer in YYYYMMDD format using a Math column (see above)
- Create a new date from this integer with a âText to Dateâ computed column.
- 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
First day of the month at midnight
Now
-DAY(Now)+1
-HOUR(Now)/24
-MINUTE(Now)/1440
-SECOND(Now)/86400
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 the last day of the 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 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
(((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.
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.
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.
((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.
