# 📅 Guide on Dates & Times **URL:** \ **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:** 1\ **Showing post:** 1
**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")
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. --- _[View the full topic](https://community.glideapps.com/t/guide-on-dates-times/70381)._