List of Important Date and Time Calculations

Community Resources
1

I have always struggled with Date and Time calculations on Glide because these formulas aren’t easy to remember. Therefore, I am compiling a list of commonly used formulas here and will keep updating this post with more formulas related to Date and Time Calculations.

  • YEAR(D)*100+MONTH(D) = To get Month and Year from a date. Mostly used to create Monthly Dashboards.

  • (YEAR(Date-1)*100)+WEEKNUM(Date-1) = To get weeknumber from a date. Mostly used to create Weekly Dashboards.

  • YEAR(Date)*10000+MONTH(Date)*100+DAY(Date) = To convert Date and Time column to just Date Column. This is mainly used to remove the TIME from Date and Time picker column.

  • round((end-start)*24,2) = To get Total Hours between 2 Dates. Mainly used for Time Tracking Apps.

1 Like
2

I have countless date math formulas that I’ve done throughout the years in the forum. Many have been modified and improved throughout the years. If anybody has links to any posts with the more interesting formulas, please share, because I’ve never kept track of them.

Here is one of the more popular formulas. I recently made a very small tweak that not only accounts for leap years, but also should account for leap centuries that occur every 400 years. What this does is allow you to add months to a date. This accounts for months that are shorter than the starting month and back dates to the end of the month. For example, if you start with Dec 31st and add 2 months, you should end up with Feb 28th or Feb 29th instead of Mar 2nd or Mar 3rd. Otherwise it will match the day number if it can.

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

Here is another version of the above formula, but it does not back date to the last day of the month. Instead it will rollover to the next month. It’s similar to the previous formula, but it excludes the second half of the formula with takes care of the back date correction. For example, adding 2 months to Dec 31st of this year will result in Mar 2nd instead of Feb 29th. Otherwise it will match the day number if it can.

((Date-DAY(Date)+15)+TRUNC(Months/12*365.2424))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.2424))
+
DAY(Date)

Here are a couple that I put together for @Lucas_Pires recently.

Pass in any year to get the first day of that year. (Jan 1st)

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

Pass in any year to get the final day of that year. (Dec 31st)

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

You may ask why some of these are so complicated for what they do and why not use some of the other plugins, such as Format Date, Text to Date, or javascript, but the reason I try to do it purely in a math column is that it is fast, it works across all devices, and it doesn’t get confused by regional differences such as DDMMYYYY or MMDDYYYY date formats. All dates remain in a glide date format. I just find it faster and more trustworthy than some of the date related plugins.

If I come across more, or if there is something specific that someone is looking for, I’ll try to add them here. I just started working on a glide app to compile many of these formulas so I at least have a central place to store them, but nothing that is published yet.

6 Likes