How to get the dates of the current monday, wednesday and friday of the current week in excel?

I'd just like a simple formula to return the date for the current Monday, Wednesday and Friday based on the current date.

So, I would like to see it like:

Cell A1 I'd like (Today is Monday January 9 2012)
Cell B1 I'd like (Wednesday January 11 2012)
Cell C1 I'd like (Friday January 13 2012).

MetroidFan2002

Posted 2012-01-10T04:49:59.407

Reputation: 214

Do you mean the Monday, Wednesday and Friday of the current week? – Paul – 2012-01-10T05:09:18.947

Answers

The following will display the dates for Monday, Wednesday and Friday of the current week:

=NOW() - WEEKDAY(NOW(),3)
=NOW() - WEEKDAY(NOW(),3)+2
=NOW() - WEEKDAY(NOW(),3)+4

Basically this is taking the time now, and subtracting the current weekday (which gives you Monday), then adds 2 days or 4 days to get Wednesday and Friday.

Paul

Posted 2012-01-10T04:49:59.407

Reputation: 52 173

You may consider using TODAY() instead of NOW() as the latter returns the time as well, which make it slightly less clean if further computation is needed. – Kenneth L – 2016-12-22T02:03:39.097

Sunday of this current week:

=1-WEEKDAY(TODAY())+TODAY()

Monday of this current week:

=2-WEEKDAY(TODAY())+TODAY()

Tuesday of this current week:

=3-WEEKDAY(TODAY())+TODAY()

Wednesday of this current week:

=4-WEEKDAY(TODAY())+TODAY()

Thursday of this current week:

=5-WEEKDAY(TODAY())+TODAY()

Friday of this current week:

=6-WEEKDAY(TODAY())+TODAY()

Saturday of this current week:

=7-WEEKDAY(TODAY())+TODAY()

SUNDAY of following week, when Monday & not Sunday is used as day #1 of week:

=8-WEEKDAY(TODAY())+TODAY()

Also, if you change the format of the cell to a custom format and choose DDD it will display Fri or Mon and if you use DDDD it will display Friday or Monday, etc.

opsin

Posted 2012-01-10T04:49:59.407

Reputation: 1 401

When using the weekday function with a Return_Type here are the options: no ,Return_type = = 1 (Sunday) through 7 (Saturday) 1 = 1 (Sunday) through 7 (Saturday) 2 = 1 (Monday) through 7 (Sunday) 3 = 0 (Monday) through 6 (Sunday) – opsin – 2012-01-10T05:47:21.663

If you want it to say today is something or another:

=CONCATENATE("Today is ",TEXT(6-WEEKDAY(TODAY())+TODAY(),"DDDD"),".")

This would return: Today is Friday.

And this:

=CONCATENATE("Today is ",TEXT(6-WEEKDAY(TODAY())+TODAY(),"DDDD MMM DD"),".")

Would return: Today is Friday Jan 13.

opsin

Posted 2012-01-10T04:49:59.407

Reputation: 1 401