Formula to return day of the week from date and time
I found a formula that would return the day of the week from the date located in a cell. How can I get this to work with the date and time in the cell?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TEXT function will work on a date or a date/time, for example try this formula
=TEXT(NOW(),"ddd")
NOW() contains the current date and time
If TEXT function doesn't work for you then your "date" isn't a valid date, try converting with "text to columns"
Select date column
Data > text to columns > OK
Now try the formula again, does that work?
regards, barry
=TEXT(NOW(),"ddd")
NOW() contains the current date and time
If TEXT function doesn't work for you then your "date" isn't a valid date, try converting with "text to columns"
Select date column
Data > text to columns > OK
Now try the formula again, does that work?
regards, barry
ASKER
I meant to say since the cell has the time in it as well the formula does not work. If I took the time out then it works fine. Unfortunately I cannot remove the time since it's needed in my report.
Delete the space between the date and the time and then type back a single space then see if it works.
ASKER
I guess I am not explaining myself correctly. I have a date and time in cell A1, I want to put a formula in A2 to display the day of the week for the date in A1.
That formula will work equally well whether it is only a data or it is a date/time
If it is not working then there is something wrong with the date/time. To check that enter the formula
=A1+1
It should give you the next date with the same time. If this is working then the text() function will also work.
If it is not working then there is something wrong with the date/time. To check that enter the formula
=A1+1
It should give you the next date with the same time. If this is working then the text() function will also work.
ASKER
OK I got it. It seems there was some hidden text in the cell which I did not see. Thanks for all the help.
Check out this file with both scenarios.
datetimeday.xlsx
datetimeday.xlsx
ASKER
=text(a1,"ddd") this did not work.
A1 = 8/24/2012 12:00 AM This is a similar format of the cell.