How do I add a unit suffix to the hours an employee has worked?

Question

I'm looking for a formula to count hours and set up a shift plan My idea is for managers, front of house and back of house to have their own planner. Where I fall at is when I try to count hours for each staff member BUT also make it legible to the reader when printed

For example Staff member 4 does 0830-1600 with an hour break on Monday 1200-1900 with no break on Tuesday I want it to look like 0830-1600, but in the total hours to count out the whole week from the times put in each day

Answer 1

I think you need to re-design your calendar. Excel loves raw data to be split out onto separate rows so let's start with that.

This is my end goal - it's a simplified version of yours

---

Data

So let's set out the data that will get us there - in rows. For this example, paste the data into cell A6:

Name    Day      Time In  Time Out  Total
Name 1  09/01/17    08:00   18:00   10:00
Name 1  12/01/17    11:00   15:00   04:00
Name 1  13/01/17    12:00   21:00   09:00
Name 2  10/01/17    08:00   13:00   05:00
Name 2  11/01/17    12:00   20:00   08:00
Name 2  13/01/17    10:00   14:00   04:00

And then add another few columns onto the end which will be useful in getting totals and making the table more readable

Unique ID is created simply by concatenating name and day : =A7&B7. And the Time String uses the TEXT function

=UPPER(TEXT(C7,"hAM/PM")&"/"&TEXT(D7,"hAM/PM"))

---

Table

Create your table in A1:F3 (like below). No need for manual shift data but do put your people and dates headings in:

Use this simple formula in cell B2 and drag down and across to populate shift data

=IFERROR(VLOOKUP($A2&B$1,$F$6:$G$12,2,0),"OFF")

Similarly, you can use this formula for the total hours

=SUMIF($A$7:$A$12,$A2,$E$7:$E$12)*24

---

Result

The final result is below, and can be easily manipulated for more people/ dates, etc.

If you have questions about any of these formulas/ techniques -- do ask