Dashpivot offers the following formulas that handle and manipulate date and time values in your forms and templates. These formulas are especially useful for:
Scheduling
Data Analysis
Calculations
Time Tracking
Alerts
Formatting
Make use of date and time formulas in Dashpivot to streamline your forms and make processes much easier to track.
Function ID | Description | Syntax | Sample | Results | Notes |
DATE | Returns the specified date as the number of full days since nullDate. | DATE(Year, Month, Day) | Ex. 1 =DATE(2024,06,1) ____________ Ex. 2 = DATE (A1, B1, C1) *A1, B1, C1 - number field/list Given that, A1 = 2024 B1 = 7 C1 = 1 | Ex. 1 Returns value, 45444.00 (The serial number of 2024/06/01 ~ YYYY/MM/DD)
_________ Ex. 2 Returns value, 45474.00 (The serial number of 2024/07/01) ~ YYYY/MM/DD) | nullDate = 1899,12,30
In order to display the date in a specific format, use TEXT(). For example: =TEXT(DATE(A1, B1, C1), “YYYY/MM/DD”) It should be clear what your month and day are. It is safer to use a dropdown list and specify the month and day. |
DATEDIF | Calculates the number days between two dates. | DATEDIF(Date1,Date2) | =DATEDIF (A1,B1) Given that, (DD/MM/YYYY) A1 = 01/08/2024 B1 = 06/06/2023 | Returns value, 422.00 (in days) | Date1 and Date2 can be interchanged and produce the same output |
DATEVALUE | Parses a date string and returns it as the number of full days since nullDate. | DATEVALUE(“DD/MM/YYYY”) | Ex. 1 =DATEVALUE("05/11/2025") _________ Ex. 2 = DATEVALUE(TEXT(A1, “DD/MM/YYYY”) *A1 - date field Given that, (DD/MM/YYYY) A1 = 31/12/2024
| Ex. 1 Returns value, 45966.00 (The serial number of 05/11/2025 ~ DD/MM/YYYY) ____________ Ex. 2 Returns value, 45657.00 (The serial number of 31/12/2024 ~ DD/MM/YYYY) | Only supports DD/MM/YYYY format, meaning when date is converted into a text. In referencing a cell, use [=DATEVALUE(A1, “DD/MM/YYYY”)] |
DAY | Returns the day of the given date value. | DAY(Number) | =DAY(A1) *A1 - Date field Given that, A1 = 02/08/2024 | Returns the value, 2.00 (Because 02/08/2024 is the second day of the month) | Only supports the format DD/MM/YYYY. |
DAYS | Calculates the difference between two date values. | DAYS(Date2, Date1) | =DAYS(B1,A1) *A1 & B1 - Date fields Given that, A1 = 01/08/2024 B1 = 02/08/2024 | Returns the value, 1.00 | Format can’t be interchanged as it outputs a negative number if Date1 is greater than Date 2 |
DAYS360 | Calculates the difference between two date values in days, in 360-day basis. | DAYS360(Date2, Date1[, Format]) | = DAYS360(A1, B1) *A1 & B1 - Date fields Given that, A1 = 01/03/2024 B1 = 01/09/2024 | Returns the value, 180 | 0 - Considers 31 as 1 day 1 - Doesn’t include 31 Note: Date 2 is < than Date 1, otherwise the output is a negative number. |
EDATE | Shifts the given startdate by given number of months and returns it as the number of full days since nullDate. | EDATE(Startdate, Months) | =EDATE(A1,B1) *A1 - Date field *B1 - number of months to be added Given that, A1 = 02/08/2024 Number = 2 | Returns value, 45567.00
| Output is numeric form of shifted date. Use TEXT function to output date format, such as: [=TEXT(EDATE(A2,B2),"DD/MM/YYYY")] |
EOMONTH | Returns the date of the last day of the month which falls months away from the start date. | EOMONTH(Startdate, Months) | =EOMONTH(A1,Month) *A1 - Date field *Month - no. of months before ( - ) after ( + ) Given that, A1 = 05/08/2024 (in DD/MM/YYYY) Month = -3 | Returns value, 45443.00 | Output is numeric form of shifted date. Use TEXT function to output date format, such as: [=TEXT(EOMONTH(A1, B1),"DD/MM/YYYY")] |
HOUR | Returns hour component of given time. | HOUR(Time) | =HOUR(A1) *A1 - Time field Given that, A1 = 01:00 | Returns value, 1.00 |
|
INTERVAL | Converts seconds to Year, Month, Day, Hour, Minutes, Seconds | INTERVAL(Seconds) | =INTERVAL(A1) *A1 - Number field Given that, A1 = 36000 | Returns value, PT1H | 86400 = 1 Day 3600 = 1 Hour 60 = 1 Minute |
ISOWEEKNUM | Returns an ISO week number that corresponds to the week of year. | ISOWEEKNUM(Date) | =ISOWEEKNUM(A1) Given that, A1 = 05/08/2024 (DD/MM/2024) | Returns value, 32.00 (Because August 5, 2024 falls in the 32nd week of 2024 in ISO system) | The week begins on a Monday. The first Thursday of the week is the first week of the year. |
MINUTE | Returns minute component of given time. | MINUTE(Time) | =MINUTE(A1) *A1 - Time field Given that, A1 = 8:30 | Returns value, 30.00 |
|
MONTH | Returns the month for the given date value. | MONTH(Number) | =DAY(A1) *A1 - Date field Given that, A1 = 05/08/2024 | Returns value, 8.00 |
|
NETWORKDAYS | Returns the number of working days between two given dates. | NETWORKDAYS(Date1, Date2[, Holidays]) | =NETWORKDAYS(A1, B1) *A1 & B1 - Date fields Given that, A1 = 01/01/2024 B1 = 31/01/2024 | Returns value, 23.00 | Working days exclude Saturday and Sunday (weekends) and can also exclude any specified holidays. Date1 < Date2 = positive value Date>1 Date 2 = negative value |
NETWORKDAYS.INTL | Returns the number of working days between two given dates. | NETWORKDAYS.INTL(Date1, Date2[, Mode [, Holidays]]) | =NETWORKDAYS.INTL(A1, B1, 1, Table1!D:D) *A1, B1, Table1!D:D - Date fields Given that, A1 = 01/01/2024 A2 = 31/01/2024 C1 = 1 D1 = 05/01/2024 D2 = 15/02/2024 | Returns the value, 21.00 (The number of working days between January 1, 2014 and January 31, 2024 is 23 days. The weekend mode set is '1' there fore Saturdays and Sundays are the weekends. Deducting the indicated holidays in our date fields in Table 1 column D (2 dates), we have 21 working days.) | Number values for weekend argument (Mode):
Mode is also set in the formula. It cannot be a Number field. |
NOW | Returns current date + time as a number of days since nullDate. | NOW() | Ex. 1 =NOW() _______ Ex. 2 =TEXT(NOW(), “DD/MM/YYYY”) | Returns value, 45509.96 (Given that today is August 5, 2024. The 45509 is the serial number of the date. The 0.96 is the serial number of the time.) | Use TEXT function to output date format, such as: [=TEXT(EOMONTH(A1, B1),"DD/MM/YYYY")] |
SECOND | Returns second component of given time. | SECOND(Time) | =SECOND(NOW()) | Returns value, 51.00 (At the time of test) | This may not be applicable to the time fields in DP as there is no seconds included. However, when using NOW() it will return the second component. |
TIME | Returns the number that represents a given time as a fraction of full day. | TIME(Hour, Minute, Second) | Ex. 1 =TIME(A1, B1) *A1 & B1 - Number fields Given that, A1 = 23 B1 = 59 ___________ Ex. 1 =TEXT(TIME(A1, B1), “HH:MM”) Given that, A1 = 23 B1 = 59 | Ex. 1 Returns the value, 1.00 _________ Ex. 1 Returns the value, 23:59 | Use TEXT function to output date format, such as: [=TEXT(TIME(A1, B1, ), "HH:MM")] |
TIMEDIF | Calculates distance in hours between two times. | TIMEDIF(Time1,Time2) | =TIMEDIF(A1, B1) *A1 & B1 - Time fields Given that, A1 = 01:00 B1 = 2:00 | Returns the value, 1.00 | The return value units is in hours, unless you convert it into minutes (*60) or seconds (*3600). |
TIMEVALUE | Parses a time string and returns a number that represents it as a fraction of full day. | TIMEVALUE(Timestring) | =TIMEVALUE("3:30") | Returns the value, 0.15 | Does not work for Time or work fields. Will only work if the time string is included in the formula. |
TODAY | Returns an integer representing the current date as the number of full days since nullDate. | TODAY() | Ex. 1 =TODAY() _______ Ex. 2 =TEXT(TODAY(), “DD/MM/YYYY”) | Ex. 1 Returns the value, 45509.00 ________ Ex. 2 Returns the value, 05/08/2024 | Use TEXT function to output date format, such as: [=TEXT(TODAY(), "DD/MM/YYYY")] |
WEEKDAY | Computes a number between 1-7 representing the day of week. | WEEKDAY(Date, Type) | =WEEKDAY(A1, 1) *A1 - Date field Given that, A1 = 05/08/2024 | Returns the value, 2.00 (August 5, 2024 is a Monday and the 2nd day of the week according to Type 1.) | Types:
Type is typed in the formula and cannot be in a number field. |
WEEKNUM | Returns a week number that corresponds to the week of year. | WEEKNUM(Date, Type) | =WEEKNUM(A1) *A1 - Date field Given that, A1 = 25/12/2024 | Returns the value, 52 (December 25, 2024 falls under the 52nd week of the year.)
| Types:
Type is typed in the formula and cannot be in a number field. |
WORKDAY | Returns the working day number of days from start day. | WORKDAY(Date, Shift[, Holidays]) | Ex. 1 =WORKDAY(A1, B1, Table1!A:A) *A1, Table1!A:A - date field *B1 - Number field Given that, A1 = 06/08/2024 B1 = 10 Table1 A1 = 06/08/2024 Table1 A2 - 07/08/2024 __________ Ex. 2 =TEXT(WORKDAY(A1, B1, Table1!A:A), DD/MM/YYYY”) | Ex. 1 Returns value, 45525.00 (Serial number of 21/08/2024) _______ Ex. 2 Returns value, 21/08/2024 | Use TEXT function to output date format, such as:
[=TEXT(WORKDAY(A1, B1, Table1!A:A), "DD/MM/YYYY")] |
YEAR | Returns the year as a number according to the internal calculation rules. | YEAR(Number) | =YEAR(A1) *A1 - Date field Given that, A1 = 05/05/2024 | Returns value, 2024.00 |
|
YEARFRAC | Computes the difference between two date values, in fraction of years. | YEARFRAC(Date2, Date1[, Format]) | =YEARFRAC(A1, B1) *A1 & B1 - Date field Given that, | Returns value, 1.00 | Format:
|