Home |
Search |
Today's Posts |
#1
|
|||
|
|||
date formatting?
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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") |
#4
|
|||
|
|||
One way:
=TEXT(A1+3,"\MON m/d") In article , tkaplan 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. |
#5
|
|||
|
|||
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 . com, "Dave O" 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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting, date. | Excel Worksheet Functions | |||
Date formatting problem | Excel Discussion (Misc queries) | |||
Conditional formatting of date | Excel Discussion (Misc queries) | |||
Incorrect Date formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting (Date vs Number) | Excel Discussion (Misc queries) |