Day & Month

[Bill Ridgeway]

In B4 & B5 I have the formula -
=TODAY()
which, correctly, returns 07/05/2007

In C4 I have the formula -
=DAY(B4)
which, incorrectly, returns Saturday (it is Monday)

What have I done wrong here please?

Thanks.

Bill Ridgeway

 

[Niek Otten]

Hi Bill,

Don't use the DAY() function; it returns 7, which is interpreted as 7-1-1900, which is a Saturday.
Use =B4 instead and format custom as dddd

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In B4 & B5 I have the formula -
| =TODAY()
| which, correctly, returns 07/05/2007
|
| In C4 I have the formula -
| =DAY(B4)
| which, incorrectly, returns Saturday (it is Monday)
|
| What have I done wrong here please?
|
| Thanks.
|
| Bill Ridgeway
|
|

 

[Bill Ridgeway]

Thanks Neik. I was working on the assumption that if =Year(A1) returned
2007, =DAY(A1) would return Monday. Who said computers are consistent?
There is consistency, however, in adopting your suggestion and formatting
the cell to return the correct output!

Here's another one for if you don't mind. The formula -
=DATE(YEAR(F11),MONTH(F11),1)
returns, correctly, the first day of the current month

So why doesn't-
=DATE(YEAR(F11),1,MONTH(F11))
return the first day of the current year?

Thanks..

Bill Ridgeway

 

[Dave Peterson]

=day(date(2007,12,25)) will return 25. The number of the day in that month.

=DATE(YEAR(F11),1,1)

Will return the first day of the year for the date in F11.

=DATE(YEAR(F11),1,MONTH(F11))
will return Jan 1, Jan 2, ... or Jan 12--depending on the month of the date in
F11.

 

[Niek Otten]

Hi Bill,

With the current date in F11,

=DATE(YEAR(F11),1,1)
returns the first day of the current year.

Your formula would (today) use 5 for the day.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Thanks Neik. I was working on the assumption that if =Year(A1) returned
| 2007, =DAY(A1) would return Monday. Who said computers are consistent?
| There is consistency, however, in adopting your suggestion and formatting
| the cell to return the correct output!
|
| Here's another one for if you don't mind. The formula -
| =DATE(YEAR(F11),MONTH(F11),1)
| returns, correctly, the first day of the current month
|
| So why doesn't-
| =DATE(YEAR(F11),1,MONTH(F11))
| return the first day of the current year?
|
| Thanks..
|
| Bill Ridgeway
|
| | > Hi Bill,
| >
| > Don't use the DAY() function; it returns 7, which is interpreted as
| > 7-1-1900, which is a Saturday.
| > Use =B4 instead and format custom as dddd
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > | In B4 & B5 I have the formula -
| > | =TODAY()
| > | which, correctly, returns 07/05/2007
| > |
| > | In C4 I have the formula -
| > | =DAY(B4)
| > | which, incorrectly, returns Saturday (it is Monday)
| > |
| > | What have I done wrong here please?
| > |
| > | Thanks.
| > |
| > | Bill Ridgeway
| > |
| > |
| >
| >
|
|

 

[Erny]

Hi Bill,

I would use:

=WEEKDAY(B4)

Kind regards,
Erny

 

[Niek Otten]

That will give you a number, not a name.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Bill,
|
| I would use:
|
| =WEEKDAY(B4)
|
| Kind regards,
| Erny
|
| | > In B4 & B5 I have the formula -
| > =TODAY()
| > which, correctly, returns 07/05/2007
| >
| > In C4 I have the formula -
| > =DAY(B4)
| > which, incorrectly, returns Saturday (it is Monday)
| >
| > What have I done wrong here please?
| >
| > Thanks.
| >
| > Bill Ridgeway
| >
|
|

 

[ato_zee]

Day of the week etc can be tricky, I have a
Format Cells -> Custom
Put in the box
dddd dd.mmm
and if I put in the formatted cell
7/5
it evaluates correctly to
Monday 07 May
(I'm in the UK where it's day before month,
should work in the US I guess)
I put two spaces between
ddd and dd.mmm and that seems to be followed
in the output.
I was surprised I didn't need to use the
WEEKDAY function.
Might be worth experimenting with.

 

[Erny]

Hi again,

In fact, when you have in one cell a comlete date such as 7-May-2007, Excel
will use a number (say 39209), and represent this number according to the
format specidied for the cell.

i.e. if you formatted "dd-mmm-yyyy", the result displayed will show
07-May-2007, but the underlying information will still be the number 39209.

When you use the function DAY(B4), Excel assumes you are using a format
according to the DATE function (example: if you use "dd-mm-yy" it would
expect something like 07/05/07 for instance). However, if it encounters
only a single number, it assumes it is the day (in my example), and will
evaluate as follows:

for the number 1, it will evaluate Sunday (equivalent to 1-Jan-1900), which
is correct
for the number 2, it will evaluate Monday (equivalent to 2-Jan-1900), which
is correct
....
for the number 31, it will evaluate Tuesday (which is correct for
31-Jan-1900)
for the number 32, it will evaluate again 1, since it would be the begin of
a new month, but since no other month (no second number) is specified, it
will recalculate DAY(1) which re-evaluates to Sunday, but does not
correspond to 1-Feb-1900
33 will be retranslated into 2 etc.
After another 29 days it will again restart with 1, etc.

You could easily test this out when representing it individually in
different columns; Excel help also provides extensive explanations about the
expectations of the orginal cell in case of using the function DAY(..)

Hope it helps,
Erny