# Calculate time difference

#### tcfd1166

##### Board Regular
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
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.

#### Akashwani

##### Well-known Member
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

#### tcfd1166

##### Board Regular
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.

#### Akashwani

##### Well-known Member
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

#### tcfd1166

##### Board Regular
It works great for everything after 5PM. It doesn't work for before 8 AM.

#### barry houdini

##### MrExcel MVP
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

#### Akashwani

##### Well-known Member
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

#### Akashwani

##### Well-known Member
Hi Barry,

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

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

Thanks

Ak

#### tcfd1166

##### Board Regular
Thanks, they both worked!

Replies
2
Views
205
Replies
15
Views
219
Replies
1
Views
199
Replies
7
Views
136
Replies
5
Views
327

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.

### Which adblocker are you using?

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

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