Help

Find the next occurrence of a specific day

Topic Labels: Dates & Timezones
1794 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Justin2
4 - Data Explorer
4 - Data Explorer

Having issues coming up with the following formula need.

I have a “Delivery Date” field with a date. I have another field where the forumla will be “Settlement Date”

The Settlement Date needs to look at the Delivery Date and then automatically calculate next weeks Friday date.

Issue is the work week is Monday-Sunday and pay day, or “Settlement Date”, is the next week on a Friday.

Example 1. Delivery Date = Sunday 12/12/21 then Settlement Date = 12/17/21
Example 2. Delivery Date = Monday 12/13/21 then Settlement Date = 12/24/21

1 Reply 1

Hi there,

this would be the formula for the date of next Friday based on today:

DATEADD(TODAY(),IF(WEEKDAY(TODAY())>5,12-WEEKDAY(TODAY()),5-WEEKDAY(TODAY())), 'days')

Cheers
Rupert