Previous Topic Next Topic

Excel Formula output text which includes date

Closed
DJack - Jun 15, 2009 at 04:19 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 27, 2013 at 11:28 AM
Hello,

My problem is that I would like the cells that are included in my text to return in the format of a date.

=IF([cell]="","",""&[cell]&" - "&[cell]&"")

This should return, for example "9-15-2009 - 9-17-2009", instead it returns "40071.3333333333 - 40073.7083333333"

Is there anything I can do to see dates?

Thanks!

3 responses

Answer 1 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 18, 2010 at 09:59 AM
Try this

=TEXT(A1, "MM-DD-YYYY") & " - " & TEXT(B1, "MM-DD-YYYY")

A1, B1 refers to cells which contained the data.
9
MuntainDW
Oct 29, 2010 at 02:02 PM
I use this all the time +1 best answer!

the format section "MM-DD-YYYY" follows standard date formatting, so if you wanted the month and day only, you could change it to "MMMM DD" and you woud end up with "September 15" as the output.
0
LincM83
Mar 7, 2013 at 10:44 PM
Can this formatting rule be used to add "th" "nd" and "st"? i.e. 20th, 22nd, 21st. If so, what syntax would you use?
0
affe
Jun 20, 2013 at 10:32 PM
I'm using Excel 2013. The error message came out. I copied exactly as your text.
0
Jan Dee
Aug 26, 2013 at 09:35 AM
Just want to ask, what if, if you want year 1 or year 99, is it possible. If so, how? Please tell me. thanks :)
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 27, 2013 at 11:28 AM
@Jan Dee,
Use YY instead of YYYY.
0
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 15, 2009 at 08:44 PM
format those resulting cells as Date and choose any one on the right side

excel takes the dates as integer from january 1, 1900 depending upon the ocnfiguration in you compuer.

see help "about dates and datasystems"
1
Guest
Jul 16, 2010 at 12:50 AM
venkat, you're right in a way, but that doesn't solve the problem. Because once you enter a formula that outputs text (as in the example), Excel will use the number which represents the date and doesn't force the date format. I haven't found a solution to this yet, either
0
Answer 3 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 17, 2010 at 11:12 PM
will it be possible for you to park a small extract of your sheet in speedyshare.com(delete password)
1

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
formulas for 1st and 2nd position in class
jack -
suff954 -

1 response
Unsupported signal. Please check device output
S.Agnew -
Yaya68 -

14 responses