Dynamic Retrival Date

[grail0305]

For example the input Date is July 15, 2025 how can I create a formula that will get the previous Sunday and Monday of the given Date:

Input : July 15, 2025

Output:

Previous Sunday: July 13, 2025

Previous Monday: July 7, 2025

can you anyone help using the formula tool 

thanks in advance

[binu_acs · Accepted solution]

@grail0305 

prev Sunday:

DateTimeAdd([InputDate], -ToNumber(DateTimeFormat([InputDate], "%w")), "days")

Prev Monday:

DateTimeAdd([InputDate], -(
IIF(ToNumber(DateTimeFormat([InputDate], "%w")) = 0,
6,
ToNumber(DateTimeFormat([InputDate], "%w")) - 1)
), "days")

[grail0305]

@binu_acs Thanks the formula for sunday is ok, but on the prev monay there is an issue, with regards to the parenthesis;

[aatalai · Accepted solution]

@grail0305 you could try this previous monday

DateTimeAdd([Date],

Switch(DateTimeFormat([Date],"%A"),Null(),"Monday",-7, "Tuesday",-8,"Wednesday",-9,"Thursday",-10,"Friday",-11,"Saturday",12,"Sunday",-12),

"day")

previous sunday

DateTimeAdd([Date],

Switch(DateTimeFormat([Date],"%A"),Null(),"Monday",-7, "Tuesday",-8,"Wednesday",-9,"Thursday",-10,"Friday",-11,"Saturday",-12,"Sunday",-13),

"day")

previous sunday

[binu_acs · Accepted solution]

@grail0305 You missed the datetime add part

DateTimeAdd([InputDate], -(
IIF(ToNumber(DateTimeFormat([InputDate], "%w")) = 0,
6,
ToNumber(DateTimeFormat([InputDate], "%w")) - 1)
), "days")