Day formula

[JICDB]

What's going on with the Day formula for 2010? I am working in Excel 2003
and the formula that I always use to pull the day of the week isn't working.
I thought it might be my spreadsheet but I tried it in a blank one and got
the same thing.

Cell E3 has date 01/28/2010 formatted as a date. Cell G3 references cell E3
with this formula =DAY(E3) and it is formatted as custom DDDD - so that I see
the day of the week. January 28, 2010 is a THursday and it returns a
Saturday. When I format as a date only I get January 28th 1900. What am I
doing wrong. It is not set to 1904 date system (when I do that I get 2014).

 

[Glenn]

What's going on with the Day formula for 2010? I am working in Excel 2003
and the formula that I always use to pull the day of the week isn't working.
I thought it might be my spreadsheet but I tried it in a blank one and got
the same thing.

Cell E3 has date 01/28/2010 formatted as a date. Cell G3 references cell E3
with this formula =DAY(E3) and it is formatted as custom DDDD - so that I see
the day of the week. January 28, 2010 is a THursday and it returns a
Saturday. When I format as a date only I get January 28th 1900. What am I
doing wrong. It is not set to 1904 date system (when I do that I get 2014).

If you just want the day of the week spelled out, format the cell as DDDD and
enter the whole date.

 

[David Biddulph]

=DAY() has never returned the day of the week. It returns the day of the
month. If you are struggling to understand what an Excel function does,
type its name into Excel help (unless the function is DATEDIF). In this
case, type DAY into Excel help.

 

[T. Valko]

The day number 28 returned from the date 1/28/2010 is being evaluated as the
date serial number 28 which when formatted as date is Jan 28 1900 which is a
Saturday.

Excel store dates as interger offsets from a base date. Using the default
date system that base date is Jan 1 1900. Jan 1 1900 has the numeric value
of 1. Jan 2 1900 has the numeric value of 2, Jan 3 1900 has the numeric
value of 3, etc., etc. Jan 28 2010 has the numeric value of 40206. It's the
40206th day since the base date of Jan 1 1900.

You can see the true numeric value of a date by formatting the cell that
contains the date as General or Number.

So, to get the weekday for a date you can try this:

=TEXT(E3,"ddd")

 

[Mike H]

Hi,

I think what you want is
=Weekday(E3)
formatted as DDDD returns Thursday

Day(E3) would simply return 28
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.