The DAY function is crook

  • Thread starter Thread starter Belch
  • Start date Start date
B

Belch

I am using the day function in Excel 97. I enter a date, say 3/29/04,
and custom reformat it to dddd which shows the day of the week,
Monday.

On the next line I use the DAY function to reference the date in cell
above it and come up with Sunday. What's wrong?

Belch
 
Hi Belch

DAY from that date returns 29. If you treat that as a date, Excel thinks of
it as January 29 1900. Which <drumroll> was a sunday.
 
Hi Belch!

DAY returns the day of the month.

DAY(A1) where A1 is TODAY() returns 29

If you format as dddd:

=MOD(29,7)
Returns 1

Date Serial Number 1 is Sunday

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi
use the function
=WEEKDAY(A1)
to get the day number
The function DAY returns a number betwenn 1 and 31 and not the weekday
 
Hi Harald!

<drumroll> followed by <whoopee cushion> <vbg>

Actually Jan-1-1900 was a Monday and Jan-29-1900 was a Monday too!

It's just that Excel copied the date serial number error from Lotus
1-2-3.

I all corrects itself after the non-existent 29-Feb-1900 but in the
meantime there are occasions when we can use it to our advantage as
when we format dddd the return of WEEKDAY(A1,1) which gives us the
right day of the week for the weekday number.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Dave!

Double Doh!

Next time you put the clocks back an hour, make sure that you don't
put the day back 1 as well <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Dave!

Yours is a more robust approach that works with 1900 and 1904 date
systems.

If we assume a 1900 Date System easier alternatives:

=WEEKDAY(A1,1)
Format dddd or ddd
Preserves underlying date

Or:

=TEXT(WEEKDAY(A1,1),"ddd")
Produces text.

I'm relying on the error in Excel that treats 1-Jan-1900 as a Sunday
when in fact it was a Monday. An error that gets corrected after the
non-existent 29-Feb-1900

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Frank!

That's because you're in the Northern reaches of the Northern
Hemisphere. Spring forwards, Fall back.

It's Autumn (aka US term "Fall") here. Not so as you'd notice with
temps still hitting close to 30C every day.

Australian Eastern Standard Time is now UTC + 10:00 without Summer
Time adjustment. Frankfurt I think is UTC + 1:00 plus 1 hour Summer
Time adjustment making you 8 hours behind us (and way past your
bedtime).

At least in recent years, countries have got together to standardize
the date at which clocks are changed (last Sunday in March. There's
now only a few hours where the difference changes because we've gone
back and you've not gone forwards. Also in recent years Windows
appears to have got the clock change right for Australia; in Windows
95 it went the wrong way.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top