Calculate time

[Excelexcel86]

Hi guys quick question how do I calculate time but only first 6 hours from two dates like so

02/03/2023 10:30 02/03/2023 15:30.

Obviously if I just minus the two cells I get 5 hours but I need it to stop calculating anything after 12:00 on the same day so it should be 1.30 hours

Any help be much appreciated

 

[James006]

Hi,
With your times in cells A1 and A2, you could test

=INT(A2)+MIN(0.5,MOD(A2,1))-A1

 

[awoohaw]

Soemthing like this:

mr excel questions 12.xlsm
	A	B	C
1	2023-02-03 10:30	44960.4375	<< Date/Time Value
2	2023-02-03 15:30	44960.64583	<< Date/Time Value
3	1900-01-00 01:30		<<DIFFERENCE to 12 Noon
4	01:30:00		<< As a time only
5	0.0625		<< Date/Time Value
Sheet10

Cell Formulas
Range	Formula
B1:B2	B1	=A1
A3	A3	=IF(A2-INT(A2)>0.5,0.5,A2-INT(A2))-(A1-INT(A1))
A4	A4	=IF(A2-INT(A2)>0.5,0.5,A2-INT(A2))-(A1-INT(A1))
A5	A5	=IF(A2-INT(A2)>0.5,0.5,A2-INT(A2))-(A1-INT(A1))

MOD Function wlll work as well:
=IF(Mod(A2),1)>0.5,0.5,Mod(A2,1)-Mod(A1,1))

 

[Excelexcel86]

Hi that part works great but I forgot to add that that some days hours are 8 hours worked

 

[awoohaw]

Hi that part works great but I forgot to add that that some days hours are 8 hours worked

so, what is the RULE to subtract from noon instead of a different time? Do the hours ever go beyond midnight?

 

[Excelexcel86]

Hi yes the times go over midnight as well

 

[awoohaw]

you didn't answer the other part. how do I know to stop at 12 noon or keep going?
Is it if the hours difference is greater than 8? What if start in the afternoon.

I don't think you are giving enough information for someone to give an answer in one response. Please elaborate.

 

[awoohaw]

please use the xl2bb add and mock up some start and end times and expected results. If cannot xl2bb then put the data in a table that we can copy, not an image.

 

[Excelexcel86]

you didn't answer the other part. how do I know to stop at 12 noon or keep going?
Is it if the hours difference is greater than 8? What if start in the afternoon.

I don't think you are giving enough information for someone to give an answer in one response. Please elaborate.

 

[Excelexcel86]

Hi just sent a picture