Calculate time difference

tcfd1166

Board Regular
Joined
Jul 27, 2007
Messages
67
Office Version
  1. 365
Platform
  1. Windows
I have a Clock In/Out app on my phone that exports to excel. I need to calculate the extra time spent working that is before or after regular business hours and I cannot figure it out. I need to know the amount of time that is extra before 8AM and after 5PM in minutes. The app exports the time recorded as h:mm AM/PM.

I cannot figure out how this formula would work, any help here?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
Put 8:00 AM in A1, 5:00 PM in A2 and just subtract your actual start and finish times from these. Add them all up and that's the extra work you've done.

Assuming your start time is in B1, for example, then =MINUTE(A1-B1) would give you additional minutes worked before 8am for that day.
 
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

Is this what you mean?...

Excel Workbook
ABCD
1InOutExtra
27:00 AM5:00 PM1:00
38:00 AM5:00 PM
48:00 AM6:00 PM1:00
57:00 AM6:00 PM2:00
6
Sheet11


Copy the formula down.

I hope that helps.

Ak
 
Upvote 0

tcfd1166

Board Regular
Joined
Jul 27, 2007
Messages
67
Office Version
  1. 365
Platform
  1. Windows
This works if I come in before 8AM, but if I start my time after 8AM, it doesn't work correctly. Ex. 10 AM to 6 PM in this instance would still be 1, since it is after 5PM.

Here are the examples I am working with.

Check-In Check-Out
10:45 AM 11:48 PM
6:45 AM 11:45 PM

In the first, I checked in at 10:45AM but still need everything to be included that is not from 8AM to 5PM.
 
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
ADVERTISEMENT
Hi,

Does this work?....

Excel Workbook
ABCD
1InOutExtra
27:00 AM5:00 PM1:00
38:00 AM5:00 PM
48:00 AM6:00 PM1:00
57:00 AM6:00 PM1:00
610:45 AM11:48 PM6:48
76:45 AM11:45 PM6:45
810:00 AM6:00 PM1:00
9
Sheet11


Ak
 
Upvote 0

tcfd1166

Board Regular
Joined
Jul 27, 2007
Messages
67
Office Version
  1. 365
Platform
  1. Windows
It works great for everything after 5PM. It doesn't work for before 8 AM.
 
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
ADVERTISEMENT
Assuming both times are on the same day try this formula

=(MEDIAN(0,"8:00"-A2,B2-A2)+MEDIAN(0,B2-"17:00",B2-A2))*24
 
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

Does this solve the problem?...

Excel Workbook
ABCD
1InOutExtra
27:00 AM5:00 PM1:00
38:00 AM5:00 PM
48:00 AM6:00 PM1:00
57:00 AM6:00 PM2:00
610:45 AM11:48 PM4:03
76:45 AM11:45 PM8:00
810:00 AM6:00 PM1:00
9
Sheet11


Copy the formula down.

Ak
 
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi Barry,

Thanks for jumping in, I was starting to see () when I closed my eyes!! :ROFLMAO:

I knew there would be a simpler way, I have such a long road to travel!!

Thanks

Ak
 
Upvote 0

Forum statistics

Threads
1,195,710
Messages
6,011,243
Members
441,596
Latest member
reza_57

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
Top