Date formula

[jbrown021286]

Is there a formal that can take a date referenced in a cell then output the date of the previous Sunday? For example cell a1 has =today() ( let's say it is outputting the date 2/14/2024 ) I would need the formula in b1 to output the date 2/11/2024.

 

[Phuoc]

Try this in B1:

=WORKDAY.INTL(A1,-1,"1111110")

 

[awoohaw]

This was also described in your earlier question, here:

should this formula work

i am looking to make a formula that references a worksheet labeled aggregated data and i am wanting to add all the numbers in row 4 column c-ag if the date in the corresponding cells on row 3 are between the start of the week and today with the week starting on Sunday. this is the formula i...

www.mrexcel.com

In POST #4.

 

[awoohaw]

Try this in B1:

=WORKDAY.INTL(A1,-1,"1111110")

if the start date is a Sunday and you want that as your answer the formula needs to be updated by advancing the start date one day:

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

 

[jbrown021286]

if the start date is a Sunday and you want that as your answer the formula needs to be updated by advancing the start date one day:

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

Thanks

if the start date is a Sunday and you want that as your answer the formula needs to be updated by advancing the start date one day:

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

That updated formula outputs the same date as the refrance date. A1,-1 comes back with the last Sunday date. Is there a way to make it where if the input date in a1 is a Sunday it count that as the last sunday?

 

[awoohaw]

I don't understand your question. If the reference date is Sunday, do you want to start with that date as the "last Sunday"? Maybe you can give a table of expectations:
Which do you want to see for the expected "Last Sunday" of 2/18. If you're doing a Week To Date starting 2/18, I would think you want 2/18?

Reference Date:	Sun 2/18	Mon 2/19	Tue 2/20	Wed 2/21	Thu 2/22	Fri 2/23	Sat 2/24
Expected Last Sunday	Sun 2/18 or 2/11	Sun 2/18	Sun 2/18	Sun 2/18	Sun 2/18	Sun 2/18	Sun 2/18

If you want 2/18: =WORKDAY.INTL(A1+1,-1,"1111110")
If you want 2/11 =WORKDAY.INTL(A1,-1,"1111110")

 

[jbrown021286]

Sry about that wasn't thinking about it being after midnight and not Saturday anymore. You formula worked perfectly thanks

 

[Peter_SSs]

I think this one also does what you want?

=A1+1-WEEKDAY(A1+7)

 

[Phuoc]

=A1+1-WEEKDAY(A1+7)

A little shorter:

=A1+1-WEEKDAY(A1)

 

[Peter_SSs]

A little shorter:

=A1+1-WEEKDAY(A1)

Yes, of course!