Week Calculations - find the Sunday

[Cowells01]

I have a date which if it falls between Thursday of week 1 and Wednesday of week 2 I need the date of the Sunday of week 2. But if its pre Wednesday in week 1 then I need the Sunday of week 1. Does that make sense?

 

[jasonb75]

Try this with your date in A1

=WORKDAY.INTL(A1+3,1,"1111110")

 

[Cowells01]

Try this with your date in A1

=WORKDAY.INTL(A1+3,1,"1111110")

Cheers Jason...That seems to work perfectly! Would you mind explaining how it does?

 

[jasonb75]

Sure, A1+3 is advancing the original date by 3 days so wednesday becomes saturday, thursday beocmes sunday etc.
"1111110" tells the workday.intl function that sunday is the only working day (the 7 digits represent the week form monday to sunday, with 0 representing working days and 1 representing weekend days).

The function then returns the first working day after the specified date. Wednesday week 1 + 3 days = saturday week 1 so next working day is sunday week 1. Thursday week 1 + 3 days = sunday week 1 so next working day is sunday week 2.

Hope that makes sense.

 

[Sulprobil]

Mappe1
	C
1	So 05.01.2020
Tabelle1

Cell Formulas
Range	Formula
C1	C1	=CEILING(A1+3,7)+1

if you do not use the 1904 date system.