Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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.

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

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

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

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.

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

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.

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

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. :)

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.