Counting Hours on a Employee Schedule Time Sheet

[chrisderby123]

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]

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]

Thanks for the reply.

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]

What exactly does your "time" cell contain?

 

[chrisderby123]

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]

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]

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