24 hr clock help

[bl1nd]

Hi all, I have a schedule that uses 24 hour clock (military time) and I need to have the length of each shift calculated.

I wanted to enter the time without having to use the colon each time, so I formatted the cells using custom and then "0000". This allows me to have their shift look like (as an example) 0500 - 1430

If I have the start of their shift (0500) in cell D4 and the end of their shift (1430) in cell E4. How would I calculate the length of their shift in decimal format so that the end result would be 8.5?

- note - The schedule does not need to be inputted in 24 hour clock, it can be entered as 5am - 2.30pm, however I would prefer 24 hour clock if possible.

Thank you in advance!

 

[shg]

Welcome to the board.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

Out​

[/td][td="bgcolor:#F3F3F3"]

Hours​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

500​

[/td][td]

1430​

[/td][td="bgcolor:#E5E5E5"]

9.50​

[/td][td]C2: =MOD(DOLLARDE(B2/100, 60) - DOLLARDE(A2/100, 60), 24)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1430​

[/td][td]

500​

[/td][td="bgcolor:#E5E5E5"]

14.50​

[/td][td][/td][/tr]
[/table]

 

[bl1nd]

Thanks for your fast response!

This works great, only one thing I have noticed...

I am also using this formula to calculate the time difference between two shifts (I need to ensure that there is at least 8.0 hours in between two shifts).
It works in most instances, however, in one example I have the following;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0500[/TD]
[TD="align: center"]1330[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]2330[/TD]
[/TR]
</tbody>[/TABLE]

When I use your formula;
=IF(N2="","",IF(M2="","",MOD(DOLLARDE(N2/100,60)-DOLLARDE(M2/100,60),24)))
*edited slightly so that it only runs if there is a value present*
It gives me a value of 1.5 instead of 25.5.

How would I edit it so that it calculates it properly?

 

[shg]

It gives me a value of 1.5 instead of 25.5.

How much time is there between 13:30 and 15:00?

 

[JoeMo]

Thanks for your fast response!

This works great, only one thing I have noticed...

I am also using this formula to calculate the time difference between two shifts (I need to ensure that there is at least 8.0 hours in between two shifts).
It works in most instances, however, in one example I have the following;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0500[/TD]
[TD="align: center"]1330[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]2330[/TD]
[/TR]
</tbody>[/TABLE]

When I use your formula;
=IF(N2="","",IF(M2="","",MOD(DOLLARDE(N2/100,60)-DOLLARDE(M2/100,60),24)))
*edited slightly so that it only runs if there is a value present*
It gives me a value of 1.5 instead of 25.5.

How would I edit it so that it calculates it properly?

If the "Out" in col M is always on the day before the "In" in col N then:

=IF(N2="","",IF(M2="","",MOD(DOLLARDE(N2/100,60)-DOLLARDE(M2/100,60),24)+IF(N2>=M2,24,0)))

 

[bl1nd]

How much time is there between 13:30 and 15:00?

Between 13:30 on Tuesday & 15:00 on Wednesday, there is 25.5...

 

[shg]

How could a formula know that it's a day later and not the same day or a week later?

 

[bl1nd]

If the "Out" in col M is always on the day before the "In" in col N then:

=IF(N2="","",IF(M2="","",MOD(DOLLARDE(N2/100,60)-DOLLARDE(M2/100,60),24)+IF(N2>=M2,24,0)))

Great, thanks!

 

[bl1nd]

Great, thanks!

Ohhh... one more issue... occasionally there will be a shift that finishes past midnight...

Basically this is a normal work schedule for employees, so there is space for a in and a out time of each day of the week. So in the example above, yes, col M is always the day before col N, however sometimes there will be shifts that run past midnight and into the next day.

Eg-

[TABLE="width: 200"]
<tbody>[TR]
[TD]Monday IN[/TD]
[TD]Monday OUT[/TD]
[TD]Tuesday IN[/TD]
[TD]Tuesday OUT[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]0130[/TD]
[TD]0800[/TD]
[TD]1430[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there anyway to do this? :/

 

[JoeMo]

Ohhh... one more issue... occasionally there will be a shift that finishes past midnight...

Basically this is a normal work schedule for employees, so there is space for a in and a out time of each day of the week. So in the example above, yes, col M is always the day before col N, however sometimes there will be shifts that run past midnight and into the next day.

Eg-

[TABLE="width: 200"]
[TR]
[TD]Monday IN[/TD]
[TD]Monday OUT[/TD]
[TD]Tuesday IN[/TD]
[TD]Tuesday OUT[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]0130[/TD]
[TD]0800[/TD]
[TD]1430[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]


Is there anyway to do this? :/

Excel Workbook
	L	M	N	O	P
1	Monday IN	Monday OUT	Tuesday IN	Tuesday OUT	Time Between Shifts
2	1500	0130	0800	1430	6.5
3	1500	1830	0800	1530	13.5
Sheet1