Combining dates and times

[kweaver]

This has always been a nightmare for me.
It's causing all kinds of issues with another situation I posted. Maybe this will help me resolve it.

Book1
	D	E	F
14	02/21/2022	11:00 AM	2/26/2022 11:00
15	02/21/2022	01:00 PM	2/26/2022 13:00
16	02/21/2022	01:00 PM	3/6/2022 13:00
17	02/21/2022	09:00 PM	3/6/2022 21:00
18	02/22/2022	09:00 PM	3/15/2022 21:00
19	02/22/2022	05:00 AM	3/16/2022 5:00
20	02/24/2022	05:00 AM	3/26/2022 5:00
21	02/24/2022	01:00 PM	3/26/2022 13:00
22	02/21/2022	08:00 AM	2/21/2022 8:00
23	02/21/2022	11:00 AM	2/21/2022 11:00
Sheet1

Cell Formulas
Range	Formula
F14:F23	F14	=D14+E14

I thought date and time columns could be put together using + per the above. Clearly not because the dates are messed up.
Why did some of the dates jump to the next month?

How to I get one column to consist of the merge of the dates and times?

 

[Alex Blakenburg]

You should be able to normally it depends on how your times were generated. Your times have days in them eg the values aren't just the fraction they have a preceding integer.
If you are happy they represent just hours and mins then you can try this:
(be careful in that they may represent duration and not a time of day)

=D14+MOD(E14,1)

If you wanted play it really safe in case you have similar issues with the date

=INT(D14)+MOD(E14,1)

 

[kweaver]

Thanks, Alex...how did you see the preceding integer?

I will try your suggestions.

As a follow-up: If I have =D18+MOD(E18,1) how do I add 8 hours to that?

 

[Alex Blakenburg]

Change the column format to general or paste special values.
It tends to happen automatically when you paste in XL2BB

 

[Dave Patton]

Date and Time 2021.xlsm
	D	E	F
12	Converted to Dates	Formatted General
13	21-Feb-2022	5.45833333	21-Feb-2022 11:00 AM
14
13d

Cell Formulas
Range	Formula
F13	F13	=INT(D13)+MOD(E13,1)

Date and Time 2021.xlsm
	D	E	F	G	H
12	Converted to Dates	Formatted General	Time portion
13
14	21-Feb-2022	5.45833333	11:00:00 AM	21-Feb-2022 11:00 AM	21-Feb-2022 11:00 AM
15	21-Feb-2022	5.54166667	01:00:00 PM	21-Feb-2022 01:00 PM	21-Feb-2022 01:00 PM
16	21-Feb-2022	13.5416667	01:00:00 PM	21-Feb-2022 01:00 PM	21-Feb-2022 01:00 PM
17	21-Feb-2022	13.875	09:00:00 PM	21-Feb-2022 09:00 PM	21-Feb-2022 09:00 PM
18	22-Feb-2022	21.875	09:00:00 PM	22-Feb-2022 09:00 PM	22-Feb-2022 09:00 PM
19	22-Feb-2022	22.2083333	05:00:00 AM	22-Feb-2022 05:00 AM	22-Feb-2022 05:00 AM
20	24-Feb-2022	30.2083333	05:00:00 AM	24-Feb-2022 05:00 AM	24-Feb-2022 05:00 AM
21	24-Feb-2022	30.5416667	01:00:00 PM	24-Feb-2022 01:00 PM	24-Feb-2022 01:00 PM
22	21-Feb-2022	0.33333333	08:00:00 AM	21-Feb-2022 08:00 AM	21-Feb-2022 08:00 AM
23	21-Feb-2022	0.45833333	11:00:00 AM	21-Feb-2022 11:00 AM	21-Feb-2022 11:00 AM
24
13d

Cell Formulas
Range	Formula
F14:F23	F14	=MOD(E14,1)
G14:G23	G14	=D14+F14
H14:H23	H14	=INT(D14)+MOD(E14,1)