Date/Time concatenation


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
 
Ad

Advertisements

M

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
 
Ad

Advertisements

S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top