Date/Time concatenation

  • Thread starter Thread starter Al
  • Start date Start date
A

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
 
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
 
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.
 
Back
Top