Excel Formula output text which includes date

Closed
DJack - Jun 15, 2009 at 04:19 PM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 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 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
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
805
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
805
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