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

 

[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")

 

[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"))