Date/Time concatenation

[Al]

Hello,

Cell A1 = YR (YYYY)
A2= Month (M) 1-12 no leading zero
A3= Day (D) 1-31 no leading zero
A4= Hour (H) 1-24 no leading zero

How do I create a date/time (dd/mm/yyyy HH:MM) from these columns
I know Date(a1,a2,a3) will gice me the date but how do I add the time as well?

Thanks

 

[Gary''s Student]

=DATE(A1,A2,A3)+TIME(A4,0,0) and format to include BOTH date & time.

 

[Mike H]

Hi,

You don't say where the minutes come from for the time so this assumes 00
minutes

=TEXT(DATE(A1,A2,A3),"dd/mm/yyyy ")&TEXT(TIME(A4,0,0),"hh:mm")

Mike

 

[Al]

Thanks!

=DATE(A1,A2,A3)+TIME(A4,0,0) and format to include BOTH date & time.

 

[Shane Devenshire]

Hi,

Here is another solution:

=(A2&"-"&A3&"-"&A1)+A4/24

or using this idea:

=DATE(A1,A2,A3)+A4/24

Although longer the first one has the advantage that you can enter the year
as 1 or 2 digits in A1. The second one returns 1909 if you enter 9.