MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can you get Excel to return the name of the Day when you give it a date?


Posted by Chris Rock on August 31, 2001 8:33 AM

I have a date, and I'd like Excel to tell me what day of the week it is, for example, if it's 8/31/01 it would tell me "Friday".

The WEEKDAY formula gives me a number, and I suppose I could reference a lookup table that matches these numbers to days of the week. But I'd like to avoid that if I can.

I doubt if this is possible without a custom VBA function or a Vlookup table, but if anyone knows how, it would be nice.

Thanks.


Posted by IML on August 31, 2001 8:37 AM

either format your cell as dddd
or point this formula to your date (in A1)
=TEXT(A1,"dddd")

good luck

Posted by Barry Katcher on August 31, 2001 9:48 AM

Enter the date, say in cell C2.

Then set up a cell, say C3, with the formula referencing cell C2
=WEEKDAY(C2,1)
where C2 is the date cell and 1 is the return-type (with 1 being Sunday thru 7 being Saturday). This will return, for example, a 6 for 8/31/2001.

In another cell, say C4, enter the following formula:
=IF(C3=1,"Sunday",IF(C3=2,"Monday",IF(C3=3,"Tuesday", etc.)))))))
This will return the day. Not elegant but effective.

Posted by Dax on September 01, 2001 3:58 AM

You could do it with one formula:-

=CHOOSE(WEEKDAY(C3),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

You could change the order of the days if you wanted a different number for day 1 than Sunday.

Regards,
Dax.


Posted by Aladin Akyurek on September 02, 2001 6:54 AM

Dax -- The bit that you add (see below) isn't needed, is it? The order of the days that you used in the formula reflect precisely the order WEEKDAY(C3) [without the optional arg unfilled] will default to. :)