Solved

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

Posted on 2012-08-29
1,003 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

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 14

Author Comment

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

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

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 14

Author Comment

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

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

LVL 14

Author Comment

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

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 14

Author Closing Comment

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

Check out this file with both scenarios.
datetimeday.xlsx
0

## Featured Post

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…