24 hr clock help

B

bl1nd

New Member
Joined
Jul 17, 2016
Messages
32
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!
 
Sort by date Sort by votes
JoeMo said:
Sheet1

*LMNOP
Monday INMonday OUTTuesday INTuesday OUTTime Between Shifts

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:57px;"><col style="width:57px;"><col style="width:58px;"><col style="width:58px;"><col style="width:63px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]0130[/TD]
[TD="align: right"]0800[/TD]
[TD="align: right"]1430[/TD]
[TD="align: right"]6.5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1830[/TD]
[TD="align: right"]0800[/TD]
[TD="align: right"]1530[/TD]
[TD="align: right"]13.5[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
P2=IF(OR(M2="",N2=""),"",IF<span style=" color:008000; ">(M2<l2,mod<span style=" color:#0000ff; ">(DOLLARDE(N2/100,60)-DOLLARDE(M2/100,60),24)</l2,mod,MOD(DOLLARDE(N2/100,60)-DOLLARDE(M2/100,60),24)+IF(N2>=M2,24,0)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...





Awesome! Works great, thank you so much!
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Trying to perfect this slowly to fit my needs... is there a way to adapt this formula to work with the following two additional criteria...

Current formula:
=IF(E4="","",MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24))

Criteria #1
Formula changed so that it subtracts 0.5 hours from the result, only if the result is greater or equal to 5.5

Currently I have the above formula in cells F4 & F5 and it gives me the following results;

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Monday In[/TD]
[TD="align: center"]Monday Out[/TD]
[TD="align: center"]Shift Length[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0500[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"]8.5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0600[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]4.0[/TD]
[/TR]
</tbody>[/TABLE]

I would ideally like the the result in F4 to be 8.0 and the result in F5 to stay 4.0

Criteria #2
A staff member works a double / split shift on the same day.
Right now it would look like the following on the schedule;

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Monday In[/TD]
[TD="align: center"]Monday Out[/TD]
[TD="align: center"]Shift Length[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]8.5 (would like to be 8.0)[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0600-1200[/TD]
[TD="align: center"]1800-2330[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

Obviously this is not going to work because the formula is not designed to deal with this... does anybody have any suggestions on the best way to approach this?

Thank you for any help in advance!
 
Upvote 0
bl1nd said:
Trying to perfect this slowly to fit my needs... is there a way to adapt this formula to work with the following two additional criteria...

Current formula:
=IF(E4="","",MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24))

Criteria #1
Formula changed so that it subtracts 0.5 hours from the result, only if the result is greater or equal to 5.5

Currently I have the above formula in cells F4 & F5 and it gives me the following results;

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Monday In[/TD]
[TD="align: center"]Monday Out[/TD]
[TD="align: center"]Shift Length[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0500[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"]8.5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0600[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]4.0[/TD]
[/TR]
</tbody>[/TABLE]

I would ideally like the the result in F4 to be 8.0 and the result in F5 to stay 4.0

Criteria #2
A staff member works a double / split shift on the same day.
Right now it would look like the following on the schedule;

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Monday In[/TD]
[TD="align: center"]Monday Out[/TD]
[TD="align: center"]Shift Length[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]8.5 (would like to be 8.0)[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0600-1200[/TD]
[TD="align: center"]1800-2330[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

Obviously this is not going to work because the formula is not designed to deal with this... does anybody have any suggestions on the best way to approach this?

Thank you for any help in advance!
Click to expand...


Looks like I have solved the first criteria with the following;

=IF(E4="","",IF(MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)>5.4,MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)-0.5,MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)

:)
 
Upvote 0
bl1nd said:
Criteria #2
A staff member works a double / split shift on the same day.
Right now it would look like the following on the schedule;

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Monday In[/TD]
[TD="align: center"]Monday Out[/TD]
[TD="align: center"]Shift Length[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]8.5 (would like to be 8.0)[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0600-1200[/TD]
[TD="align: center"]1800-2330[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

Obviously this is not going to work because the formula is not designed to deal with this... does anybody have any suggestions on the best way to approach this?

Thank you for any help in advance!
Click to expand...

Would the formulas on the following thread work for my needs?

http://www.mrexcel.com/forum/excel-...ple-numbers-contained-within-single-cell.html

I cannot figure this one out!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Spreadsheet with lots of formulas and named ranges takes time to load and clocks each time a cell is updated
2
Replies
11
Views
171
NSRL
N
M
  • Question Question
Splitting AM and PM hours from a Date Range
Replies
4
Views
580
mcmuney
M
M
  • Locked
  • Question Question
Trying to input time calculation so it does not change to 24 hour format
Replies
3
Views
176
Peter_SSs
Peter_SSs
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
Alex Blakenburg
A
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
188
dallas2699
D

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top