Calculate hours across dates using separate "date cells"

[Carlos5]

Hello all,
I have searched for time calc threads but none seem to answer my question. I would like to calculate the difference in hours between two possibly, but not always different dates and military times. But I would like the date to be entered in it's own cell. so 4 cells total: Start date, start time, end date, end time. I realize it is end time - start time but how do i include the dates in the calc? Thanks for any help.
Carlos

 

[Micron]

You have to calculate using the entire date/time value for the starting and ending times. Date/times are floating point double data types. To see the decimal value for Now (at least at that moment):
?cdbl(now)
44759.5740856482

To see the decimal value of Date:
?cdbl(Date)
44759
You cannot hope to get accurate calculations for elapsed time without the portion to the right of the decimal, which is the time portion of the date value (the date value being left of the decimal). Personally I see no point in separating date/time into columns. Anyone should be able to decipher the time from the entire value and separating them forces you to reconstruct the date/time in order to perform time span calculations.

 

[Carlos5]

So are you saying that there is no way to "add" the contents of the two cells to come up with the correct decimal value and then do the required math to figure out the hours elapsed? Is there not some way of determining the date value and "attaching" it to the time value?
Thanks

You have to calculate using the entire date/time value for the starting and ending times. Date/times are floating point double data types. To see the decimal value for Now (at least at that moment):
?cdbl(now)
44759.5740856482

To see the decimal value of Date:
?cdbl(Date)
44759
You cannot hope to get accurate calculations for elapsed time without the portion to the right of the decimal, which is the time portion of the date value (the date value being left of the decimal). Personally I see no point in separating date/time into columns. Anyone should be able to decipher the time from the entire value and separating them forces you to reconstruct the date/time in order to perform time span calculations.

 

[Micron]

No, I'm not saying you can't add them together as in A1 + B1. Perhaps that could be as simple as that - provided the time portion relates to the same day I guess, but your data is not like that, correct? So if you add/concatenate 01/01/2022 and 04:00:00 PM you'd get 4PM on that date. However, if the second time part is on the next day (e.g. 01:00:00 AM) and you do this, you'll get a value that equates to 1AM on the first, not 1AM on 01/02/2022. The time part starts over again at zero each day, and that is your basic problem. If there is a way to calculate this with the data as you have it, I don't know of one, mainly because I'd never split it in the first place if it was going to be my only columns with time and date. I might hide the complete date time columns (in and out date/times), but only if I really needed to present it as separate columns. Most likely I'd just show 01/01/2022 06:00:00 PM as the start and 01/02/2022 02:00:00 AM and call that an 8 hour duration and not have any issues. If the boss complains I'd say "I can make it look different, but I absolutely need values like that somewhere on the sheet".

 

[Carlos5]

No, I'm not saying you can't add them together as in A1 + B1. Perhaps that could be as simple as that - provided the time portion relates to the same day I guess, but your data is not like that, correct? So if you add/concatenate 01/01/2022 and 04:00:00 PM you'd get 4PM on that date. However, if the second time part is on the next day (e.g. 01:00:00 AM) and you do this, you'll get a value that equates to 1AM on the first, not 1AM on 01/02/2022. The time part starts over again at zero each day, and that is your basic problem. If there is a way to calculate this with the data as you have it, I don't know of one, mainly because I'd never split it in the first place if it was going to be my only columns with time and date. I might hide the complete date time columns (in and out date/times), but only if I really needed to present it as separate columns. Most likely I'd just show 01/01/2022 06:00:00 PM as the start and 01/02/2022 02:00:00 AM and call that an 8 hour duration and not have any issues. If the boss complains I'd say "I can make it look different, but I absolutely need values like that somewhere on the sheet".

Thank you for your time, hopefully someone else can help me with a solution. There seems to be a way to do it, I have seen it done elsewhere, I just can't figure it out.

 

[PeteWright]

EDIT:
Arrr, got some calculation error. Just one sec...

 

[PeteWright]

Book1.xlsm
	A	B	C
1	16	START	END
2	DATE	8/9/2022	8/10/2022
3	TIME	1500	0700
Sheet1

Cell Formulas
Range	Formula
A1	A1	= milhrs(B2,C2,B3,C3)

Public Function milhrs(ByVal DateA As Date, ByVal DateB As Date, ByVal TimeA As Integer, ByVal TimeB As Integer) As Integer
   Dim dh As Integer: dh = (DateValue(DateB) - DateValue(DateA)) * 24
   Dim h As Integer: h = (TimeB - TimeA) / 100
   milhrs = dh + h
End Function

Assuming a Date or String formatted as date in the date cells (with the end date being "greater" then or same as the start date) and number values in the time cells.
Custom number format on the time cells => 0000

 

[Carlos5]

Book1.xlsm
	A	B	C
1	16	START	END
2	DATE	8/9/2022	8/10/2022
3	TIME	1500	0700
Sheet1

Cell Formulas
Range	Formula
A1	A1	= milhrs(B2,C2,B3,C3)

Public Function milhrs(ByVal DateA As Date, ByVal DateB As Date, ByVal TimeA As Integer, ByVal TimeB As Integer) As Integer
   Dim dh As Integer: dh = (DateValue(DateB) - DateValue(DateA)) * 24
   Dim h As Integer: h = (TimeB - TimeA) / 100
   milhrs = dh + h
End Function

Assuming a Date or String formatted as date in the date cells (with the end date being "greater" then or same as the start date) and number values in the time cells.
Custom number format on the time cells => 0000

Thank you,
I will implement it as soon as I can.
Carlos