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![TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD] Any help would be greatly appreciated![/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Submit Date[/TD]
[TD]Closed Date[/TD]
[TD]CONC Submit[/TD]
[TD]CONC Closed[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5/1/2014 2:03:08 AM[/TD]
[TD]5/9/2014 4:34:24 PM[/TD]
[TD]=CONCATENATE(YEAR(A2),MONTH(A2),DAY(A2))[/TD]
[TD]=CONCATENATE(YEAR(B2),MONTH(B2),DAY(B2))[/TD]
[TD]=D2-C2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD] 20140501[/TD]
[TD]20140509[/TD]
[TD]8 [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.