# Counting Hours on a Employee Schedule Time Sheet

#### chrisderby123

##### New Member
Hey Team!

I've read over a few different forums and haven't found the solution that has worked properly for me, so I'm hoping something can help me with this.

I have an employee schedule that I need to be able to count the total amount of hours for each shift. If I can get a formula that works for a daily basis, then I can just make a SUM formula for the weeks totals myself. It's a restaurant, so a lot of the shifts start in the evening and end in the morning on the next day. Example: Monday 6:00PM - Tuesday 2:00AM.

This is a link to a screenshot of my spreadsheet with some random number inputs.

This is the custom format of the numbers that I have for the number inputs: 0\:00 p;0\:00 \a
So my managers just need to input 500 for 5:00 p and -300 for 3:00 a

The day of the week and dates all change when I change the date in cell D1.

Each row is a different employee.

Really just need a formula/format that will:
1. Track total hours for each shift, for each row.
2. Fast and Simple number format for the managers to input daily shift times.

Example: If a shift is from 6:30PM to 2:00AM, I need a value of 7.5.
Example: If a shift is from 10:00AM to 4:00PM, I need a value of 6.

Please let me know if you need any other information for clarification.

Thank you!
Chris

#### FDibbins

##### Well-known Member
To calc time past midnight, try this...
 A​ B​ C​ 8​ 6:00 AM​ 12:00 PM​ 6:00:00​ 9​ 12:00 PM​ 6:00 AM​ 18:00:00​ 10​ 6:30 PM​ 2:00 AM​ 7:30:00​ 11​ 10:00 AM​ 4:00 PM​ 6:00:00​
C8=MOD(B8-A8,1)
copied down

#### chrisderby123

##### New Member

Tried this and the value I am getting back is 0:00 p for all of them, and if I change it to "general number format" I get 0.

To calc time past midnight, try this...
 A​ B​ C​ 8​ 6:00 AM​ 12:00 PM​ 6:00:00​ 9​ 12:00 PM​ 6:00 AM​ 18:00:00​ 10​ 6:30 PM​ 2:00 AM​ 7:30:00​ 11​ 10:00 AM​ 4:00 PM​ 6:00:00​

<tbody>
</tbody>

C8=MOD(B8-A8,1)
copied down

#### FDibbins

##### Well-known Member
What exactly does your "time" cell contain?

#### chrisderby123

##### New Member
My time cell contains a positive or negative whole number. (200 for 2:00 a and -400 for 4:00 p)

The custom formatting on it is 0\:00 p;0\:00 \a

#### Tetra201

##### MrExcel MVP
Try the following formula -- it returns 7 for your data:

=MOD(TEXT(F8,"0\:00 p;0\:00 \a")-TEXT(E8,"0\:00 p;0\:00 \a"),1)*24

#### FDibbins

##### Well-known Member
My time cell contains a positive or negative whole number. (200 for 2:00 a and -400 for 4:00 p)

The custom formatting on it is 0\:00 p;0\:00 \a
You should try and use real time, then you can use excel's built-in time functions.

So, instead of 200 for 2:00 a, you would use 2:00 and instead of -400 for 4:00 p, you would use either 16:00 or 4:00 PM

1,081,835
Messages
5,361,596
Members
400,640
Latest member
fruitbros

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...