Day & Month

B

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
 
N

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
|
|
 
B

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
 
D

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.
 
N

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
| > |
| > |
| >
| >
|
|
 
N

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
| >
|
|
 
E

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
 
Top