MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Incredibly annoying prob ->date conversion


Posted by John Smith on January 02, 2002 8:52 AM

This is really baffling me, its so simple yet I just cant do it.
I enter a set of dates in a field, i.e 22/10/01. What I need is a set of formula to convert that into a day number, and then, using a day sheet I have created (see screenshot), convert it into a day name. Thankyou very much in advance.


Posted by Scott on January 02, 2002 8:56 AM

You should be able to re-format the date as a day. Use Format-Cells and then on the Number tab select Custom and in Type put in "dddd".

Posted by Aladin Akyurek on January 02, 2002 9:11 AM

=WEEKDAY(A1)

will give you the numbers as shown in your screenshot, and

=TEXT(A1,"ddd")

the name of the day.

Aladin

Posted by John Smith on January 02, 2002 9:12 AM

Thanks, that helps a little, but I need to do it by actually placing the day in another box. I agree its long winded but its a particular set excercise. So, in another box I need the date ie 22/10/01 converted into a day number, then using a day sheet convert into into a day name. Thx again.

Posted by Scott on January 02, 2002 9:20 AM

One more try.... Maybe....

Could you just do a link to the cell: =A1 and then format that as dddd? If it's an excercise that needs to be done that way, I'm not sure how to tranlate the date into a day number (1-365) without creating a seperate table that lists dates, and numbers and then doing a VLOOKUP to it. Maybe someone has a better suggestion??

Posted by John Smith on January 02, 2002 9:41 AM

Re: One more try.... Maybe....

Thanks guys, your life savers...

Posted by DonnaW on January 03, 2002 1:38 PM

If you are looking for the day of week ...

Which is what it looks like you have in your screen shot, why not just use the =weekday function? It returns the number of the day of week. You can then use this to lookup in your table.

Posted by Aladin Akyurek on January 03, 2002 4:20 PM

Re: If you are looking for the day of week ...

That's also what I thought. But, there is no need for a lookup. See my reply... :)

Aladin

==========