Excel to work out date for the most recent Friday 3

[LGMan]

Hi, is it possible to have a date function or formula that will display the date of the most recent Friday, by that I mean if today is Tues 17th Feb, I would like to display last fridays date which was 13/2/15, and if today was a friday, then display todays date.
Hope that makes sense.

 

[bluedragon2]

Look at the weekday function and you should with logic utilize it for what you need

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[mintjulep]

Some of the formulas here faq68-6900 may be useful as a starting point.

 

[LGMan]

Thanks, I was trying to use Weekday, but couldn't work out the logic, but have now come up with this which works unless today is a Saturday
=IF(WEEKDAY(TODAY())=6,TODAY(),(TODAY()-WEEKDAY(TODAY())-1))

 

[bluedragon2]

Just need to add the logic:

=IF(WEEKDAY(TODAY())>5,TODAY()-(weekday(today())-6),(TODAY()-WEEKDAY(TODAY())-1))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[TonyJollans]

Personally, I'd simplify it ..

=TODAY()-MOD(WEEKDAY(TODAY(),16),7)

The "16" makes WEEKDAY return 1 (Saturday) through 7 (Friday) and the MOD function changes 7 to 0 so that Fridays return today rather than last Friday.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[bluedragon2]

Nice one Tony, keep forgetting about the mod. Especially since I became management, all I do is e-mail anymore anyway

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[LGMan]

Hi BlueDragon,thanks for your persistance, your suggestion works a treat and looks like a logical improvement on mine, I need to play around with a formula like this to understand them better in the first place

Tony, thanks for the more succinct formula, I've not used MOD before,however it returns a number error, within the Evaluation it shows as 42053-MOD(#NUM!,7)
Am I missing something?

 

[combo]

Taking all from WEEKDAY function:
=TODAY()-WEEKDAY(TODAY()+3,3)

combo

 

[TonyJollans]

Well done, combo!

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[LGMan]

Thanks Combo, I'd love to understand what is happening though as although I can follow [bold]=TODAY()-WEEKDAY(TODAY()) [/bold]
As this takes 5 off the current date of 19th Feb to give 14th Feb, but what I dont understand and clearly dont know enough about formulas is the final part i.e =TODAY()-WEEKDAY(TODAY()[bold]+3,3[/bold])

 

[Andrzejek]

If you type in Excel in the cell: [tt]=WEEKDAY(5,[/tt]
You will see what you can put as the next parameter: [tt][return_type][/tt]
and 3 -> WEEKDAY returns numbers 0 - (Monday) trough 6 (Sunday)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[combo]

After adding 3 we transform Friday=>Monday, Saturday=>Tuesday, ..., Thursday=>Sunday. This sequence results WEEKDAY(Monday,3)=0, ..., WEEKDAY(Sunday,3)=6. We deduct from the current date 0 on Friday, 1 on Saturday, etc., in all cases having returned last Friday.
Needing last Sunday: =TODAY()-WEEKDAY(TODAY()+1,3), in red always the number of days to nearest Monday starting from the day we look for.

combo

 

[Salut39]

Hi,

=INT((TODAY()-2)/7)*7-1 always last Friday.

Yuri

 

[SkipVought]

U@LGMan,

I have use The formula technique that Yuri's has posted for you, for a decade or more.

I'd suggest that you study it. Tinker with it. Understand it. It is a powerful formula concept for returning a particulas day's date anytime during the week.

 

[combo]

To get current date on Friday:
=INT((TODAY()+1)/7)*7-1

combo