Blog

Dates - Examples of how to calculate dates relative to today

There's often the need to perform date calculations that are relative to the today's date. For example, what's the start date of the current week? What's the last day of the current quarter, or the last day of the current month? This post highlights several useful formulas that can help in this situation.

This post highlights several useful functions that relate to date calculations. To help explain these formulas, this post describes the output of formulas relative to the date of this post, 9th April 2021.

1. Get the current date / Get the current date and time

Two key functions are Now() and Today(). Now returns the current date and time.
Now()
Today returns the current date only, with a time value of midnight.
Today()

2. Get the start date of the current week

To get the start date of the current week (eg 5th April 2021), we can use the formula beneath.
DateAdd(Now(),
        -(Weekday(Now(),StartOfWeek.MondayZero)),
        Days
)
The weekday function returns a number that represents the weekday. The argument StartOfWeek.MondayZero specifies that we want to receive an output value of 0 (Monday) through to 6 (Sunday).


To return the date that corresponds to the Monday of the current week, we subtract the number of days that the weekday function returns from the current date.

3. Get the date of the first day in the current month

Here's the formula to return the first day of the current month (eg 1st April 2021). The Date function returns a new date, and this function expects three arguments - the year, month, and day. We specify a year and month that corresponds to the current date, and we specify a day value of 1.
Date(Year(Now()), Month(Now()), 1)

4. Get the date of the last day in the current month

To return the date that corresponds to the last day of the current month (eg 30th April 2021), we add 1 month to the first day of the current month, and subtract one day from this result.
DateAdd(
    DateAdd(
        Date(Year(Now()),Month(Now()),1),
        1,
        Months
    ),
    -1,
    Days
)

5. Get the date of the last day in the previous month

To return the date that corresponds to the last day of the previous month (eg 31st March 2021), we subtract 1 day from the first day of the current month.
DateAdd(
        Date(Year(Now()), Month(Now()), 1), 
        -1, 
        Days
)

6. Get the date of the first day in the next month

To return the date that corresponds to the first day of the next month (eg 1st May 2021), we add 1 month to the first day of the current month.
DateAdd(
        Date(Year(Now()),Month(Now()),1),
        1,
        Months
)

7. Get the current quarter

To retrieve the current quarter as a number from 1 to 4 (eg 2, in this example with an input value of 9th April), we retrieve the current month, and divide this value by 3. We then round this up to the nearest whole number by calling the RoundUp function.

RoundUp(Month(Now())/3,0)
Here are the workings for our example:

  • Month(Now()) = 4
  • 4/3 = 1.333
  • RoundUp(1.333) = 2

8. Get the start date of the current quarter

To return the date that corresponds to the first day of the current quarter (eg 1st April 2021), we construct a date with a month number that we calculate based on the quarter number.
Date(
     Year(Now()),     
     (RoundUp(Month(Now())/3,0) * 3) - 2
    ,1
)
To calculate the start month based on the quarter, we use the following formula:
Month number of start quarter = (Quarter * 3) - 2

The table beneath clarifies this calculation by showing the workings:
Quarter    Start Date    End Date         Quarter Start Month
1    01-01-2021    31-03-2021(1*3)-2= 1
2    01-04-2021    30-06-2021(2*3)-2= 4
3    01-07-2021    30-09-2021(3*3)-2= 7
4    01-10-2021    31-12-2021(4*3)-2= 10

9. Get the end date of the current quarter

To return the date that corresponds to the last day of the current quarter (eg 30th June 2021), we take the first day of the current quarter, add three months, and subtract one day.
DateAdd(
    DateAdd(
        Date(Year(Now()),
             (RoundUp(Month(Now())/3,0) * 3) - 2,
              1
        ),
        3,
        Months
    ),
    -1,
    Days
)

Further reading

For further reading, here are some other posts that are related to date calculations:

Conclusion

A common requirement is to perform date calculations that relative to the today's date. This post highlighted a selection of useful formulas.
Previous
Next