Concatenate Dates with leading zeroes

[caj1980]

I need to take a date format from a database dump and Concatenate it into a new cell to format it the way I need so I can perform calculations. I have the calculations working but I need to figure out how to insert leading zeroes for single-digit months and years so that all dates end up with the same number of characters (YYYYMMDD, not YYYYMD). I have played with custom formatting the cells using ## but can't seem to get it to work with concatenate. Row1 is column headers, Row2 is formulas, Row3 is desired results. Any help would be greatly appreciated!

Any help would be greatly appreciated!	A	B	C	D	E
1	Submit Date	Closed Date	CONC Submit	CONC Closed	Duration
2	5/1/2014 2:03:08 AM	5/9/2014 4:34:24 PM	=CONCATENATE(YEAR(A2),MONTH(A2),DAY(A2))	=CONCATENATE(YEAR(B2),MONTH(B2),DAY(B2))	=D2-C2
3			20140501	20140509	8

<tbody>
</tbody>

 

[hippiehacker]

and why not using the text function =TEXT(A2;"yyyymmdd")

 

[vds1]

Concatenate or Text treats date as string
In D2 Try,

=DATE(YEAR(B2),MONTH(B2),DAY(B2))

Then right click format cells as YYYYMMDD

 

[skorpionkz]

did ou try to use TEXT function?

Excel 2010
	A	B	C	D
1	Submit Date	Closed Date
2	05/01/2014 02:03	05/09/2014 16:34	20140105	20140905
Sheet4

Cell Formulas
Range	Formula
C2	=TEXT(A2,"yyyymmdd")
D2	=TEXT(B2,"yyyymmdd")

 

[Jonmo1]

Try

=TEXT(A2,"yyyymmdd")

However, it's not really a date anymore, just a number 20140501 (stored as a text string)

Your subtraction in E will not be accurate when the dates span 2 or more months.
i.e. Jun 9 - May 9
20140609 - 20140509 = 100, not 31


Dates are numbers by themselves, you can just do B2-A2
But since the dates in A and B contain time as well, try
=INT(B2)-INT(A2)


Hope that helps.

 

[Eric Golf]

Hi,

If A2 & B2 are recognised by Excel as Dates then you should not need to format i.e. =B2-A2 should give you the difference 8.605. if you need the time part stripped out you can use =INT(B2-A2)

Alternatively if the format is just for display then =Text(A2,"yyyymmdd") will give you 20140501.

Hope this helps,

Eric.

 

[caj1980]

TEXT function worked perfectly! Thank you! I was not aware of the TEXT function!

 

[caj1980]

Try=TEXT(A2,"yyyymmdd")However, it's not really a date anymore, just a number 20140501 (stored as a text string)Your subtraction in E will not be accurate when the dates span 2 or more months.i.e. Jun 9 - May 920140609 - 20140509 = 100, not 31Dates are numbers by themselves, you can just do B2-A2But since the dates in A and B contain time as well, try=INT(B2)-INT(A2)Hope that helps.

This worked beautifully!!! Thank you!!!

 

[Jonmo1]

Glad to help, thanks for the feedback.