Counting Hours on a Employee Schedule Time Sheet

chrisderby123

New Member
Joined
Jan 16, 2017
Messages
3
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Jan 16, 2017
Messages
3
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
 

chrisderby123

New Member
Joined
Jan 16, 2017
Messages
3
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
Joined
Oct 14, 2016
Messages
3,471
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
Joined
Feb 16, 2013
Messages
6,723
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top