Solved

# Formula to return day of the week from date and time

Posted on 2012-08-29
Medium Priority
1,013 Views
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?
0
Question by:Edward Pamias

LVL 43

Accepted Solution

Saqib Husain, Syed earned 1000 total points
ID: 38347961
You should be able to use the same formula as-is for the date time.

You can also try to convert the date-time to date by using the int() function.
0

LVL 19

Author Comment

ID: 38347981
I tried it, it did not work. This formula is for date only. Do you have something that will give me the day of the week from the date and time cell.

=text(a1,"ddd")  this did not work.

A1  = 8/24/2012  12:00 AM    This is a similar format of the cell.
0

LVL 7

Assisted Solution

ID: 38347993
You want just the day like Monday?  format the cell as custom

ddd -> Gives you short day like Mon
dddd -> long day like monday

Check out: http://office.microsoft.com/en-gb/excel-help/create-or-delete-a-custom-number-format-HP005199500.aspx
0

LVL 50

Expert Comment

ID: 38347999
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
0

LVL 19

Author Comment

ID: 38348002
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.
0

LVL 43

Expert Comment

ID: 38348011
Delete the space between the date and the time and then type back a single space then see if it works.
0

LVL 19

Author Comment

ID: 38348016
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.
0

LVL 43

Expert Comment

ID: 38348026
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.
0

LVL 19

Author Closing Comment

ID: 38348030
OK I got it. It seems there was some hidden text in the cell which I did not see. Thanks for all the help.
0

LVL 43

Expert Comment

ID: 38348031
Check out this file with both scenarios.
datetimeday.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
###### Suggested Courses
Course of the Month16 days, 18 hours left to enroll