Previous Topic Next Topic

Excel Formula output text which includes date

Closed
DJack - 15 Jun 2009 à 16:19
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 27 Aug 2013 à 11:28
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!
Related:

3 responses

Réponse 1 / 3
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
18 Jul 2010 à 09:59
Try this

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

A1, B1 refers to cells which contained the data.
MuntainDW
29 Oct 2010 à 14:02
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.
LincM83
7 Mar 2013 à 22:44
Can this formatting rule be used to add "th" "nd" and "st"? i.e. 20th, 22nd, 21st. If so, what syntax would you use?
affe
20 Jun 2013 à 22:32
I'm using Excel 2013. The error message came out. I copied exactly as your text.
Jan Dee
26 Aug 2013 à 09:35
Just want to ask, what if, if you want year 1 or year 99, is it possible. If so, how? Please tell me. thanks :)
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
27 Aug 2013 à 11:28
@Jan Dee,
Use YY instead of YYYY.
Réponse 2 / 3
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
15 Jun 2009 à 20:44
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"
Guest
16 Jul 2010 à 00:50
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
Réponse 3 / 3
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
17 Jul 2010 à 23:12
will it be possible for you to park a small extract of your sheet in speedyshare.com(delete password)

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