How to use =Date() formula for a Date value that reads as number/text format

[agohir]

Hi, I have a long formula that determines dates based on certain criteria. Within the Formula I have a Date formula as follows: DATE(MID(P14,4,4),MID(P14,2,2),MID(P14,1,1)) But the value the formula reads appears as a text e.g. 42586. Can someone tell me how to read the text as a date so I can use =Date() formula to get the exact date. Right now if a date appears as 2016/08/04 in a cell, the above formula reads the date in a text 42586 and returns the date 1910/11/11.

I've tried searching online but I'm unable to find how to interpret the text 42586 as a date.

Thanks for the help/feedback.

 

[CatOnASpreadsheet]

Hi agohir,

Excel internally handles dates as numbers, so 42586 is how it works with the date 04/08/2016. The corresponding number for 11/11/1910 is 3968. To change your number to the actual date you need to format your cell as a date. If that does not work due to your main formula returning an actual string, you can wrap your formula in a =VALUE() formula before doing the formatting.

I hope this helps.

Cheers,
Catalin

 

[FDibbins]

try this...
=text(your-formula,"YYYY/MM/DD")

What exactly is in P14 anyway?

 

[Mal4131]

Hi All,

I have a similar question. I want to subtract a date range (6-7 months) from a start date and would like it to appear in the cell as, "MM/DD/YYYY - MM/DD/YYYY". Right now the formula is returning 42491 - 42461:
=DATE(YEAR(F9),MONTH(F9)-6,DAY(F9))&" - "&DATE(YEAR(F9),MONTH(F9)-7,DAY(F9))

I tried the formula below, but no success:
=Text(DATE(YEAR(F9),MONTH(F9)-6,DAY(F9)),"mmm-dd")&" - "&Text(DATE(YEAR(F9),MONTH(F9)-7,DAY(F9)),"mmm-dd")

Thanks for any recommendations.

 

[FDibbins]

Try this...

	A​	B​	C​
26​	10/21/2016​		04/21/2017 to 05-21-2017

C26=TEXT(EDATE(A26,6),"mm/dd/yyyy")&" to "&TEXT(EDATE(A26,7),"mm-dd-yyyy")

edit: you may need to edit that to...
=TEXT(EDATE(A26,-6),"mm/dd/yyyy")&" to "&TEXT(EDATE(A26,-7),"mm-dd-yyyy")

 

[Mal4131]

Thanks FDibbins! That works! Have a good day!

 

[FDibbins]

Happy to help