DLearn how to use helpful date formulas in Dashpivot to calculate dates, durations, and working days in your templates.
Dashpivot supports Excel-style date formulas to help you automate scheduling, compliance tracking, project timelines, cut-off dates, payment schedules, alerts, and reminders.
Note: Date formulas can only be used inside Default Tables or Prefilled Tables.
DATE()
Purpose: Build a date (DD/MM/YYYY) from separate year, month, and day fields.
Formula to use: =TEXT(DATE(A1,B1,C1),"YYYY/MM/DD")
Note:
A1 = Year, B1 = Month, C1 = Day
TEXT()ensures the output matches your date field format
Useful when collecting dates via separate dropdown or number fields.
DATEDIF()
Purpose: Calculate the number of full days between two dates.
Formula to use: =DATEDIF(A1,B1)
Notes:
A1 and B1 must be Date fields
Order does not matter
Ideal for ageing reports or tracking days since an event
DAY()
Purpose: Extract the day of the month (1–31).
Formula to use: =DAY(A1)
Notes:
A1must be a Date fieldUseful for rules or calculations based on specific days
DAYS()
Purpose: Calculate the difference between two dates (Date2 − Date1).
Formula to use: =DAYS(B1,A1)
Notes:
Order matters — reversing values produces a negative result
Useful for turnaround times and deadline tracking
DAYS360()
Purpose: Calculate the number of days between two dates using a 360-day calendar.
Formula to use: =DAYS360(A1,B1)
Notes:
Commonly used for finance-related schedules
Optional format argument:
0= Includes the 31st1= Excludes the 31st
Difference between DAYS() and DAYS360()
Day1 | Day2 | Results | Formula |
12/01/2025 | 01/31/2026 | 60 |
|
12/01/2025 | 01/31/2026 | 61 |
|
EDATE()
Purpose: Shift a date forward or backward by a set number of months.
Formula: =TEXT(EDATE(A1,6),"yyyy/mm/dd")
Notes:
A1 must be a Date field
Positive numbers move the date forward
Negative numbers move the date backward
Automatically adjusts for shorter months
Example:
If A1 = 02/02/2026
=TEXT(EOMONTH(A1,1),"yyyy/mm/dd")returns 2026/08/02
EOMONTH()
Purpose: Returns the last day of a month before or after a given date.
Formula to use: =TEXT(EOMONTH(A1,1),"yyyy/mm/dd")
Notes:
A1 must contain a valid date value (not plain text)
0 → current month
Positive numbers → move forward
Negative numbers → move backward
TEXT()ensures the output matches your date field format
Example:
If A1 = 02/02/2026
=TEXT(EOMONTH(A1,1),"yyyy/mm/dd")returns 2026/03/31
WORKDAY()
Purpose: Returns a future or past working date, excluding weekends (Saturday and Sunday) and optional holidays.
Formula to use: =TEXT(WORKDAY(A1,5),"yyyy/mm/dd")
Example:
If A1=14/02/2026
=TEXT(EOMONTH(A1,1),"yyyy/mm/dd")returns 2026/02/20