?
Solved

Formula to return day of the week from date and time

Posted on 2012-08-29
10
Medium Priority
?
1,013 Views
Last Modified: 2012-08-29
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
Comment
Question by:Edward Pamias
10 Comments
 
LVL 43

Accepted Solution

by:
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

by:Edward Pamias
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

by:flaphead_com
flaphead_com earned 1000 total points
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 50

Expert Comment

by:barry houdini
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

by:Edward Pamias
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

by:Saqib Husain, Syed
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

by:Edward Pamias
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

by:Saqib Husain, Syed
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

by:Edward Pamias
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

by:Saqib Husain, Syed
ID: 38348031
Check out this file with both scenarios.
datetimeday.xlsx
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question