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 January 16, 2023 - 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 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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
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
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
I'm using Excel 2013. The error message came out. I copied exactly as your text.
0
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 January 16, 2023 544
Aug 27, 2013 at 11:28 AM
@Jan Dee,
Use YY instead of YYYY.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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
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
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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