Date Question

[tyija1995]

Hi all,

Hopefully a simple solution to this question, I want to basically return the date of the Friday just passed in "ddmmyyyy" format, so a formula to return 19072019 in the example of today's date.
If the date were Thu 12th September I'd want 06092019 - if the date is a Friday then stay as is (so Fri 13th September would be 13092019)

I recognise I can do this with a very long nested IF statement checking each day, but surely there is a more efficient way?

Thanks

Note: I want an Excel solution and NOT VBA

 

[trunten]

this should sort you

=TEXT(TODAY()+(1-WEEKDAY(TODAY(),15)),"ddmmyyyy")

 

[jmacleary]

Hi there. Try this - it assumes your date is in M2, but just replace M2 with Today() if you always want today.

=TEXT(M2-MOD(WEEKDAY(M2)+1,7),"ddmmyyyy")

 

[tyija1995]

Thank you both! Working like a charm.

 

[jmacleary]

Thanks for the feedback. Out of curiosity, which one did you go with?

 

[tyija1995]

Thanks for the feedback. Out of curiosity, which one did you go with?

Hi John,

I went with the answer trunten provided. I tried both though and yours worked too!

 

[Dave Patton]

Another approach that you can consider particularly if you do not want a text result.

Excel 2010
	D	E	F	G
2	19072019	Text
3	19072019	Custom format "ddmmyyy"
4	19072019	Custom format "ddmmyyy"
5
4a

Cell Formulas
Range	Formula
D2	=TEXT(TODAY()+(1-WEEKDAY(TODAY(),15)),"ddmmyyyy")
D3	=TODAY()+(1-WEEKDAY(TODAY(),15))
D4	=WORKDAY.INTL(TODAY(),-1,"1111011")