Formatting dates in the future

[Compass Rose]

When I enter the formula =MONTH(TODAY()) and the cell formatting is set to
General, the cell contains a 1.
When I change the cell formatting to Custom "mmm", the cell displays "Jan".
So far, so good.
When I enter the formula =MONTH(TODAY())+3 and the cell formatting is set to
General, the cell contains a 4. I would assume that this is the correct
formula for showing the month which is 3 months hence. When I change the
formatting to Custom "mmm", I would expect the cell to contain "Apr", but it
doesn't. It still displays "Jan".
When I enter the formula =MONTH(TODAY()+45) and the cell formatting is set
to General, the cell contains a 3. I would assume that this is the correct
formula for showing the month which is 45 days hence. When I change the
formatting to Custom "mmm", I would expect the cell to contain "Mar", but it
doesn't. It still displays "Jan".

I'm stumped.

TIA,
David

[richardreye]

Don't know if this is the best way to achieve what you want but it works

=DATE(YEAR(TODAY()), MONTH(TODAY())+3,DAY(TODAY()))

wil return Apr

=DATE(YEAR(TODAY()), MONTH(TODAY()),DAY(TODAY())+45)

wil return Mar

Cheers!

[Myrna Larson]

In the first formula, you are calculating a month number (1), adding 3 to it
to get 4. Then, when you format this as a date, you are telling Excel to treat
the number 4 as a date, i.e. as 4 days since "Day 0" which is Dec 31, 1899.
That's Jan 4, 1900. When you format that as "mmm" you see Jan.

In the 2nd formula you calculate a date that is 45 days from today. That's Mar
3, 2005. Again you extract the month from that date, which is 3. Then via
formatting you tell Excel to treat 3 as a date, or 3 days since 12/31/1899, or
Jan 3, 1900, and then display that date showing only the month. The result is
again Jan.

If you want to display the date 45 days from today, showing just the month,
it's

=TODAY()+45

and format the cell as "mmm". You don't use the MONTH function at all.


On Mon, 17 Jan 2005 13:55:05 -0800, Compass Rose
<CompassRose@discussions.microsoft.com> wrote:

>When I enter the formula =MONTH(TODAY()) and the cell formatting is set to
>General, the cell contains a 1.
>When I change the cell formatting to Custom "mmm", the cell displays "Jan".
>So far, so good.
>When I enter the formula =MONTH(TODAY())+3 and the cell formatting is set to
>General, the cell contains a 4. I would assume that this is the correct
>formula for showing the month which is 3 months hence. When I change the
>formatting to Custom "mmm", I would expect the cell to contain "Apr", but it
>doesn't. It still displays "Jan".
>When I enter the formula =MONTH(TODAY()+45) and the cell formatting is set
>to General, the cell contains a 3. I would assume that this is the correct
>formula for showing the month which is 45 days hence. When I change the
>formatting to Custom "mmm", I would expect the cell to contain "Mar", but it
>doesn't. It still displays "Jan".
>
>I'm stumped.
>
>TIA,
>David
>

[JE McGimpsey]

Dates in XL are stored as integer offsets from a base date (by default,
12/31/1899 in the 1900 date system).

So

=TODAY()

returns the value 38369 (try formatting the cell as General).
MONTH(TODAY()) returns the month number for the serial date 38369, or 1.
Month(TODAY()+45) returns the mont for the serial date 38414, or 3.

But when you format 3 as a date ("mmm"), XL interprets the 3 to mean 3
days after 12/31/1899 or 1/3/1900, so returns "Jan".

If you want to display "Mar", use the formula

=TODAY() + 45

and format it as "mmm"



In article <6A6CB7B8-3898-4412-A528-6E7E76A35BA1@microsoft.com>,
Compass Rose <CompassRose@discussions.microsoft.com> wrote:

> When I enter the formula =MONTH(TODAY()) and the cell formatting is set to
> General, the cell contains a 1.
> When I change the cell formatting to Custom "mmm", the cell displays "Jan".
> So far, so good.
> When I enter the formula =MONTH(TODAY())+3 and the cell formatting is set to
> General, the cell contains a 4. I would assume that this is the correct
> formula for showing the month which is 3 months hence. When I change the
> formatting to Custom "mmm", I would expect the cell to contain "Apr", but it
> doesn't. It still displays "Jan".
> When I enter the formula =MONTH(TODAY()+45) and the cell formatting is set
> to General, the cell contains a 3. I would assume that this is the correct
> formula for showing the month which is 45 days hence. When I change the
> formatting to Custom "mmm", I would expect the cell to contain "Mar", but it
> doesn't. It still displays "Jan".

[Compass Rose]

Thank you both for your help

David

"JE McGimpsey" wrote:

> Dates in XL are stored as integer offsets from a base date (by default,
> 12/31/1899 in the 1900 date system).
>
> So
>
> =TODAY()
>
> returns the value 38369 (try formatting the cell as General).
> MONTH(TODAY()) returns the month number for the serial date 38369, or 1.
> Month(TODAY()+45) returns the mont for the serial date 38414, or 3.
>
> But when you format 3 as a date ("mmm"), XL interprets the 3 to mean 3
> days after 12/31/1899 or 1/3/1900, so returns "Jan".
>
> If you want to display "Mar", use the formula
>
> =TODAY() + 45
>
> and format it as "mmm"
>
>
>
> In article <6A6CB7B8-3898-4412-A528-6E7E76A35BA1@microsoft.com>,
> Compass Rose <CompassRose@discussions.microsoft.com> wrote:
>
> > When I enter the formula =MONTH(TODAY()) and the cell formatting is set to
> > General, the cell contains a 1.
> > When I change the cell formatting to Custom "mmm", the cell displays "Jan".
> > So far, so good.
> > When I enter the formula =MONTH(TODAY())+3 and the cell formatting is set to
> > General, the cell contains a 4. I would assume that this is the correct
> > formula for showing the month which is 3 months hence. When I change the
> > formatting to Custom "mmm", I would expect the cell to contain "Apr", but it
> > doesn't. It still displays "Jan".
> > When I enter the formula =MONTH(TODAY()+45) and the cell formatting is set
> > to General, the cell contains a 3. I would assume that this is the correct
> > formula for showing the month which is 45 days hence. When I change the
> > formatting to Custom "mmm", I would expect the cell to contain "Mar", but it
> > doesn't. It still displays "Jan".
>