You are viewing limited content. For full access, please sign in.

Question

Question

Create due date based on today's date.

asked on February 26, 2024

So. I have a process that throughout the year has 4 due dates (by quarter).

Quarter 1 due date is always March 20

Quarter 2 is June 20

Quarter 3 is September 20

and Quarter 4 is December 20.

 

Based on today's date, how can I calculate:

1. What quarter we are in?

2. If the current date is past the due date?

I don't want to hard code 3/20/2024 because next year, I would have to touch the process again and change that date.  I would like a field that would have the year(now()) as the year, and let the month and day be hard coded.

 

 

0 0

Replies

replied on February 26, 2024 Show version history

This was tested in the new designer on Version 11.0.2311.50553

 

You can use a formula like this to determine what quarter a date is in. 

=CEILING(MONTH(date_field)/3)

 

If you multiply that value by 3, then you can get the number of the last month in the quater (3, 6, 9, or 12).

=CEILING(MONTH(date_field)/3)*3

 

Plug that into a DATE formula to calculate the 20th of the month. 

=DATE(YEAR(date_field),(CEILING(MONTH(date_field)/3)*3),20)

 

If you want to error proof it a bit more (to avoid errors when the date_field variable is blank), you can wrap the whole thing in an IF formula to return a blank if the variable is blank, or calculate the formula otherwise. 

=IF(date_field="","",DATE(YEAR(date_field),(CEILING(MONTH(date_field)/3)*3),20))

 

Calculating if the date is before/on the due date or after the due date is pretty easy with an IF formula. 

=IF(date_field>due_date_field,"PAST DUE!","")

 

Result looks like this:

 

EDIT TO ADD: If you always want to calculate from the current date - then everywhere I referred to the variable date_field, you should be able to use the NOW() formula instead.

4 0
You are not allowed to follow up in this post.

Sign in to reply to this post.