date formatting?

[tkaplan]

I have a cell named PeriodStart.
In a different cell i want it to say "MON" followed by the date of PeriodStart+3 in the format of m/d. So for example is period start is 8/1/2005, i want the cell to say "MON 8/4"
I did concatenate("MON ",PeriodStart+3) and it's returning MON followed by the serial number. How do i format the serial number to give me the date value?

Thank you.

[Richard Neville]

I don't think you can have two formats in the same cell. How about adding a
second cell after MON, formatted as m/d? The second cell could simply
reference the MON cell, in the format =D7 (assuming MON is C7).

"tkaplan" <[email protected]> wrote in
message news:[email protected]...
>
> I have a cell named PeriodStart.
> In a different cell i want it to say "MON" followed by the date of
> PeriodStart+3 in the format of m/d. So for example is period start is
> 8/1/2005, i want the cell to say "MON 8/4"
> I did concatenate("MON ",PeriodStart+3) and it's returning MON followed
> by the serial number. How do i format the serial number to give me the
> date value?
>
> Thank you.
>
>
> --
> tkaplan
> ------------------------------------------------------------------------
> tkaplan's Profile:
> http://www.excelforum.com/member.php...o&userid=22987
> View this thread: http://www.excelforum.com/showthread...hreadid=465916
>

[Dave O]

Here's one way to do it:
="MON "&TEXT((PeriodStart+3),"m/d")
The TEXT() function displays the numerical date value in the date
format you desire.

However, if MON is meant to indicate Monday, your period may not always
start on the prior Friday. If you need to capture the 3-letter
abbreviation in your formula, use this:
=TEXT(PeriodStart+3,"ddd")&" "&TEXT((PeriodStart+3),"m/d")

[JE McGimpsey]

One way:

=TEXT(A1+3,"\MON m/d")

In article <[email protected]>,
tkaplan <[email protected]> wrote:

> I have a cell named PeriodStart.
> In a different cell i want it to say "MON" followed by the date of
> PeriodStart+3 in the format of m/d. So for example is period start is
> 8/1/2005, i want the cell to say "MON 8/4"
> I did concatenate("MON ",PeriodStart+3) and it's returning MON followed
> by the serial number. How do i format the serial number to give me the
> date value?
>
> Thank you.

[JE McGimpsey]

Since 8/4/2005 was a Thursday, I 'm pretty sure that's not what the OP
intended, but if it were, it would be a bit simpler to use

=UPPER(TEXT(A1+3,"ddd m/d"))

In article <[email protected]>,
"Dave O" <[email protected]> wrote:

> Here's one way to do it:
> ="MON "&TEXT((PeriodStart+3),"m/d")
> The TEXT() function displays the numerical date value in the date
> format you desire.
>
> However, if MON is meant to indicate Monday, your period may not always
> start on the prior Friday. If you need to capture the 3-letter
> abbreviation in your formula, use this:
> =TEXT(PeriodStart+3,"ddd")&" "&TEXT((PeriodStart+3),"m/d")