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