Trying to get a time Difference

A

alesha711

New Member
Joined
Mar 20, 2015
Messages
17
I tried to attach the spreadsheet it won't let me. I already have formulas to extract the time. The imported data is A-F. I need column L to show the difference in times. Then in column M I want to get that difference in 6 minute increments or 1/10 of an hour. So I can compare and make sure it matches what is in Column D.

Can anyone help with this? Because I don't have AM or PM it's trowing the difference formula off and I have yet to figure out how to get it in 1/10th of an hour.

Thanks in Advance!

[TABLE="width: 2232"]
<tbody>[TR]
[TD][TABLE="width: 1364"]
<tbody>[TR]
[TD]Staff Member[/TD]
[TD]Work Code[/TD]
[TD]Date[/TD]
[TD]Input[/TD]
[TD]N/C[/TD]
[TD]Notes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/01/18[/TD]
[TD]8.80[/TD]
[TD]Yes[/TD]
[TD]in- 7:48-4:36-out
BigTime, Phone System, constant contact, melissa computer[/TD]
[TD]7:48-4:36[/TD]
[TD][/TD]
[TD]7:48-4:36[/TD]
[TD]7:48[/TD]
[TD]4:36[/TD]
[TD="align: right"]20:48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/05/18[/TD]
[TD]2.30[/TD]
[TD]Yes[/TD]
[TD]in- 7:30-9:48
Timesheets, Phone System[/TD]
[TD]7:30-9:48[/TD]
[TD][/TD]
[TD]7:30-9:48[/TD]
[TD]7:30[/TD]
[TD]9:48[/TD]
[TD="align: right"]2:18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/05/18[/TD]
[TD]1.80[/TD]
[TD]Yes[/TD]
[TD]10:30-12:18-out to lunch
BigTime keying[/TD]
[TD]10:30-12:18[/TD]
[TD][/TD]
[TD]10:30-12:18[/TD]
[TD]10:30[/TD]
[TD]12:18[/TD]
[TD="align: right"]1:48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/05/18[/TD]
[TD]3.10[/TD]
[TD]Yes[/TD]
[TD]in- 1:24-4:30-out
Keyed BigTime[/TD]
[TD]1:24-4:30[/TD]
[TD][/TD]
[TD]1:24-4:30[/TD]
[TD]1:24[/TD]
[TD]4:30[/TD]
[TD="align: right"]3:06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/06/18[/TD]
[TD]6.60[/TD]
[TD]No[/TD]
[TD]in- 10:24-5:00-out
BigTime Key[/TD]
[TD]10:24-5:00[/TD]
[TD][/TD]
[TD]10:24-5:00[/TD]
[TD]10:24[/TD]
[TD]5:00[/TD]
[TD="align: right"]18:36[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/06/18[/TD]
[TD]0.70[/TD]
[TD]Yes[/TD]
[TD]in- 8:48-9:30-out
BigTime Key[/TD]
[TD]8:48-9:30[/TD]
[TD][/TD]
[TD]8:48-9:30[/TD]
[TD]8:48[/TD]
[TD]9:30[/TD]
[TD="align: right"]0:42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]11/07/18[/TD]
[TD]1.00[/TD]
[TD]No[/TD]
[TD]in- 7:24-8:24-out
BigTime Key[/TD]
[TD]7:24-8:24[/TD]
[TD][/TD]
[TD]7:24-8:24[/TD]
[TD]7:24[/TD]
[TD]8:24[/TD]
[TD="align: right"]1:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]100 - MONTHLY BOOKKEEPING[/TD]
[TD]11/05/18[/TD]
[TD]0.70[/TD]
[TD]No[/TD]
[TD]9:48-10:30
Bank recs[/TD]
[TD][/TD]
[TD]9:48-10:30[/TD]
[TD]9:48-10:30[/TD]
[TD]9:48[/TD]
[TD]10:30[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here is the Spreadsheet with Formulas
[TABLE="width: 2738"]
<tbody>[TR]
[TD]Staff Member[/TD]
[TD]Work Code[/TD]
[TD]Date[/TD]
[TD]Input[/TD]
[TD]N/C[/TD]
[TD]Notes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43405[/TD]
[TD]8.8[/TD]
[TD]Yes[/TD]
[TD]in- 7:48-4:36-out
BigTime, Phone System, constant contact, melissa computer[/TD]
[TD]=IFERROR(TRIM(MID(F2,FIND(":",F2)-2,FIND(":",F2&":",FIND(":",F2)+1)+5-FIND(":",F2))),"")[/TD]
[TD]=IF(G2="",TRIM(MID(F2,FIND(":",F2)-1,FIND(":",F2&":",FIND(":",F2)+1)+5-FIND(":",F2))),"")[/TD]
[TD]=IF(G2="",H2,G2)[/TD]
[TD]=IFERROR(LEFT(I2,FIND("-",I2)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I2,1,FIND("-",I2),""),"")[/TD]
[TD]=MOD(K2-J2,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43409[/TD]
[TD]2.3[/TD]
[TD]Yes[/TD]
[TD]in- 7:30-9:48
Timesheets, Phone System[/TD]
[TD]=IFERROR(TRIM(MID(F3,FIND(":",F3)-2,FIND(":",F3&":",FIND(":",F3)+1)+5-FIND(":",F3))),"")[/TD]
[TD]=IF(G3="",TRIM(MID(F3,FIND(":",F3)-1,FIND(":",F3&":",FIND(":",F3)+1)+5-FIND(":",F3))),"")[/TD]
[TD]=IF(G3="",H3,G3)[/TD]
[TD]=IFERROR(LEFT(I3,FIND("-",I3)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I3,1,FIND("-",I3),""),"")[/TD]
[TD]=MOD(K3-J3,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43409[/TD]
[TD]1.8[/TD]
[TD]Yes[/TD]
[TD]10:30-12:18-out to lunch
BigTime keying[/TD]
[TD]=IFERROR(TRIM(MID(F4,FIND(":",F4)-2,FIND(":",F4&":",FIND(":",F4)+1)+5-FIND(":",F4))),"")[/TD]
[TD]=IF(G4="",TRIM(MID(F4,FIND(":",F4)-1,FIND(":",F4&":",FIND(":",F4)+1)+5-FIND(":",F4))),"")[/TD]
[TD]=IF(G4="",H4,G4)[/TD]
[TD]=IFERROR(LEFT(I4,FIND("-",I4)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I4,1,FIND("-",I4),""),"")[/TD]
[TD]=MOD(K4-J4,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43409[/TD]
[TD]3.1[/TD]
[TD]Yes[/TD]
[TD]in- 1:24-4:30-out
Keyed BigTime[/TD]
[TD]=IFERROR(TRIM(MID(F5,FIND(":",F5)-2,FIND(":",F5&":",FIND(":",F5)+1)+5-FIND(":",F5))),"")[/TD]
[TD]=IF(G5="",TRIM(MID(F5,FIND(":",F5)-1,FIND(":",F5&":",FIND(":",F5)+1)+5-FIND(":",F5))),"")[/TD]
[TD]=IF(G5="",H5,G5)[/TD]
[TD]=IFERROR(LEFT(I5,FIND("-",I5)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I5,1,FIND("-",I5),""),"")[/TD]
[TD]=MOD(K5-J5,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43410[/TD]
[TD]6.6[/TD]
[TD]No[/TD]
[TD]in- 10:24-5:00-out
BigTime Key[/TD]
[TD]=IFERROR(TRIM(MID(F6,FIND(":",F6)-2,FIND(":",F6&":",FIND(":",F6)+1)+5-FIND(":",F6))),"")[/TD]
[TD]=IF(G6="",TRIM(MID(F6,FIND(":",F6)-1,FIND(":",F6&":",FIND(":",F6)+1)+5-FIND(":",F6))),"")[/TD]
[TD]=IF(G6="",H6,G6)[/TD]
[TD]=IFERROR(LEFT(I6,FIND("-",I6)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I6,1,FIND("-",I6),""),"")[/TD]
[TD]=MOD(K6-J6,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43410[/TD]
[TD]0.7[/TD]
[TD]Yes[/TD]
[TD]in- 8:48-9:30-out
BigTime Key[/TD]
[TD]=IFERROR(TRIM(MID(F7,FIND(":",F7)-2,FIND(":",F7&":",FIND(":",F7)+1)+5-FIND(":",F7))),"")[/TD]
[TD]=IF(G7="",TRIM(MID(F7,FIND(":",F7)-1,FIND(":",F7&":",FIND(":",F7)+1)+5-FIND(":",F7))),"")[/TD]
[TD]=IF(G7="",H7,G7)[/TD]
[TD]=IFERROR(LEFT(I7,FIND("-",I7)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I7,1,FIND("-",I7),""),"")[/TD]
[TD]=MOD(K7-J7,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]001-ADMIN[/TD]
[TD]43411[/TD]
[TD]1[/TD]
[TD]No[/TD]
[TD]in- 7:24-8:24-out
BigTime Key[/TD]
[TD]=IFERROR(TRIM(MID(F8,FIND(":",F8)-2,FIND(":",F8&":",FIND(":",F8)+1)+5-FIND(":",F8))),"")[/TD]
[TD]=IF(G8="",TRIM(MID(F8,FIND(":",F8)-1,FIND(":",F8&":",FIND(":",F8)+1)+5-FIND(":",F8))),"")[/TD]
[TD]=IF(G8="",H8,G8)[/TD]
[TD]=IFERROR(LEFT(I8,FIND("-",I8)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I8,1,FIND("-",I8),""),"")[/TD]
[TD]=MOD(K8-J8,1)[/TD]
[/TR]
[TR]
[TD]Alesha Anderson[/TD]
[TD]100 - MONTHLY BOOKKEEPING[/TD]
[TD]43409[/TD]
[TD]0.7[/TD]
[TD]No[/TD]
[TD]9:48-10:30
Bank recs[/TD]
[TD]=IFERROR(TRIM(MID(F9,FIND(":",F9)-2,FIND(":",F9&":",FIND(":",F9)+1)+5-FIND(":",F9))),"")[/TD]
[TD]=IF(G9="",TRIM(MID(F9,FIND(":",F9)-1,FIND(":",F9&":",FIND(":",F9)+1)+5-FIND(":",F9))),"")[/TD]
[TD]=IF(G9="",H9,G9)[/TD]
[TD]=IFERROR(LEFT(I9,FIND("-",I9)-1),"")[/TD]
[TD]=IFERROR(REPLACE(I9,1,FIND("-",I9),""),"")[/TD]
[TD]=MOD(K9-J9,1)[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
You cant add files to this forum.
If you want to point to a file you need to upload it to an online storage site and post the link here though moderators prefer the method you have chosen.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Text Split or any other formula
Replies
8
Views
222
Cubist
Cubist
S
  • Question Question
Optimizing Price with Weightings to get to Best Price Mix - Use Solver?
Replies
1
Views
318
StephenCrump
S
K
  • Question Question
Populate With Criteria
Replies
3
Views
85
Peter_SSs
Peter_SSs
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
126
flashgordie
F
jski21
  • Solved
Find last entry in column and sum all prior rows
Replies
3
Views
310
Fluff
Fluff

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

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