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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

8​

[/td][td]

6:00 AM​

[/td][td]

12:00 PM​

[/td][td]

6:00:00​

[/td][/tr]

[tr][td]

9​

[/td][td]

12:00 PM​

[/td][td]

6:00 AM​

[/td][td]

18:00:00​

[/td][/tr]

[tr][td]

10​

[/td][td]

6:30 PM​

[/td][td]

2:00 AM​

[/td][td]

7:30:00​

[/td][/tr]

[tr][td]

11​

[/td][td]

10:00 AM​

[/td][td]

4:00 PM​

[/td][td]

6:00:00​

[/td][/tr]
[/table]
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...
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

6:00 AM​

[/TD]
[TD]

12:00 PM​

[/TD]
[TD]

6:00:00​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

12:00 PM​

[/TD]
[TD]

6:00 AM​

[/TD]
[TD]

18:00:00​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

6:30 PM​

[/TD]
[TD]

2:00 AM​

[/TD]
[TD]

7:30:00​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

10:00 AM​

[/TD]
[TD]

4:00 PM​

[/TD]
[TD]

6:00:00​

[/TD]
[/TR]
</tbody>[/TABLE]

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