Concatenate Date & Time

[BrianM]

What is the simplest way to concatenate Date A1 = 04/22/04 with B1 = 18:09:50 so that the result is 04/22/04 18:09:50


Thanks,
Brian

 

[peter_lambros]

I have a slight variation to the question. I have separate fields for date and time (registration_date, registration_time) and similarly separate fields for receipt_date and receipt_time). I want to calculate the elapsed time between registration and receipt. Registration date/time is 1 Oct 2019 at 10:00, and the receipt time is 2 Oct 2019 at 11:00. So I want a formula to calculate and show me the elapsed interval in hours:minutes, so for my example I expecting to see an elapsed time of 25:00 (25 hours:00 minutes). I have tried combining the individual cells into one value then subtracting the two, but all i see is 00:00. However if both receipt_date and registration_date are the same, the formula works. It does not work if the dates are not the same. Is there any formula that can help in calculating elapsed date/time?

 

[Peter_SSs]

Welcome to the MrExcel board!
Try a formula like this
=(C2+D2)-(A2+B2)
where C & D are receipt and A & B are registration
.. and apply a custom format to that result cell of [hh]:mm

 

[peter_lambros]

Welcome to the MrExcel board!
Try a formula like this
=(C2+D2)-(A2+B2)
where C & D are receipt and A & B are registration
.. and apply a custom format to that result cell of [hh]:mm

Thank you Peter - that worked!! For my own "education" what do the square brackets around the [hh] actually mean/enforce?

 

[James006]

Hello,

Whenever you are dealing with hours ...using [h]:mm format allows you to display number of hours greater than 24 hours ...

Hope this will help

 

[Peter_SSs]

.. what do the square brackets around the [hh] actually mean/enforce?

For the example you gave, if you remove the [] from the custom format, the result would show as 01:00 instead of 25:00
Try it and see.

 

[peter_lambros]

Hello,

Whenever you are dealing with hours ...using [h]:mm format allows you to display number of hours greater than 24 hours ...

Hope this will help

Thank you James. It now makes sense. Your help and assistance is really appreciated.

 

[peter_lambros]

For the example you gave, if you remove the [] from the custom format, the result would show as 01:00 instead of 25:00
Try it and see.

Thank you Peter. Exactly as you suggested. It now all makes sense. Thank you for your quick response and assistance; much appreciated.