Excel Formula output text which includes date

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this

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

A1, B1 refers to cells which contained the data.
9
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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.
Can this formatting rule be used to add "th" "nd" and "st"? i.e. 20th, 22nd, 21st. If so, what syntax would you use?
I'm using Excel 2013. The error message came out. I copied exactly as your text.
Just want to ask, what if, if you want year 1 or year 99, is it possible. If so, how? Please tell me. thanks :)
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
@Jan Dee,
Use YY instead of YYYY.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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"
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
will it be possible for you to park a small extract of your sheet in speedyshare.com(delete password)