Time sheet calcs Help!

[lizzardking]

Hi there.
This is my first post and hoping to get a direction to my time sheet. (attached)

Im ive got the first day setup how I want it, with task, start time , finish time, and staff member.
But I am now at a loss for creating the necessary formulas for calculations (and cell formatting) to calculate staff hours.
As you can see below the calender, I have the staff names and some basic attempts of formulas (but just the first step, for hour calcs), all coming up with errors.
Why cant my formulas calculate hours?
Can anyone help?

Kind regards
Neil

[protonLeah]

The times you have entered for your data validation (F47:F73) are text strings, not numbers. You need actual Excel time values.

[lizzardking]

Hi Ben,

I see, I must be confused.
So I must have text formatted to time in: format cells>number>time
How do I change that to Excel time values? You mean =TIMEVALUE"8:00am" ?

[Aardigspook]

Hi, welcome to the forum,

Your list of times in F47:F73 are not recognised by Excel as times. I've changed them in the attached file. (If you don't like upper case AM/PM, I'm sorry, but there's no easy way of fixing that, as Microsoft don't provide that option.) You then need to re-select the times on day 1 from the new list (I've done that on the attached file too).
RosterCal time list fixed.xlsx

I'm not quite sure what you're wanting to do with the calculations below the calendar.
A starting point is that if you want to count the number of hours done on one day, you should subtract the start time from the end time, e.g. for Helen (cell E47) it should be =C9-B9 which gives 0.38 - which is the portion of a whole day (9/24=0.375). To show this in hours, format the cell (select it and press Ctrl-1) with the custom format [h]:mm, which will give you an answer of 9:00. I've done that cell in the attached file, but haven't touched any of the others.

Hope that's of help. If you need more help with the rest of the calculations, let us know.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[lizzardking]

Hi Aardigspook and thanks.
I see, capitals for AM PM (blush)
Thanks for the solution, I never would have got that (head in shame)
however my main calculation has only just begun....

Re: The calculation below.
So I aim to calculate the individual staff working hours
As you can see, ive put a -1 hour for a lunch break, which is universal.
So the formula I have so far was where I got stuck. Calculation of hours from the drop down box.
But I have now created a logical formula with the staff as the condition.

=IF(E8=E47,(SUM(D8-C8)*24)-1,0)

But how would I make this into a range so I could use this logical test for each staff member for each time slot for the whole calendar?

Kind regards for time and help,

NeilRosterCal2.xlsx

[Aardigspook]

Hi again, sorry for the delay in replying.

I see, capitals for AM PM (blush)

am/pm are normally written in lower case (in UK & International English - not sure about American), so you were right, but unfortunately Microsoft haven't given us this option.

I never would have got that (head in shame)

Don't worry about it - this is how we all learned!

On to the problem at hand...
Unfortunately, the way your calendar is laid out makes it a bit complicated to get monthly totals*, as we're effectively dealing with 32 different tables. We need to get all of that info into a table which looks up each day, allowing the hours to then be summed.
* I understand why you've done it this way - for printing - this is an observation not a criticism.

I've therefore created a table to the right of your calendar which looks up the times selected in the calendar and converts these to hours for each day. The formulae in the table use MATCH (to check for the correct name in each section of the calendar) combined with INDEX-MATCH (to look up the correct start and end times). These are put into your formula to subtract the lunch hour and return the number of hours worked - i.e. it's still the same 'IF name matches, subtract start time from end time, times 24 for hours, subtract lunch hour'.
The final formula (for the first name, on the first day) is:

Please Login or Register  to view this content.

The various bits of the formula work like this:

IF(ISERROR(<formula>),0,<formula>)

I'm on 2003 at work, so this works like IFERROR to return 0 if the formula would otherwise return an error. You could use IFERROR instead - I just can't at the mo.

MATCH(($AJ7,$E$8:$E$13,0)

This checks if the name ('Helen') appears in the list of names on the first Monday.

INDEX($D$8:$D$13,MATCH($AJ7,$E$8:$E$13,0))
and
INDEX($C$8:$C$13,MATCH($AJ7,$E$8:$E$13,0))

These look up 'Helen' in the list of names on the first Monday and return the end time & start time. These are combined into:

(SUM(INDEX($D$8:$D$13,MATCH($AJ7,$E$8:$E$13,0))-INDEX($C$8:$C$13,MATCH($AJ7,$E$8:$E$13,0)))*24)-1

which turns the number into hours and subtracts the lunch hour.

The final formula was then copied down the 'Mon 1' column of the table. It was then amended for subsequent columns (it can't just be 'dragged' across as each day in the calendar is four columns and the rows change when you move down in the calendar).

The final column of the table (BQ) then sums the hours for each member of staff for the month. This list of totals is named 'Total_hours_for_month'. Your 'Hours' section below the calendar then looks up this column for the number of hours for each name. I've coloured both these sections blue so you can see the connection quickly.

I see you have an 'Hours' section beside the 'List of Tasks' as well. If you want this to be done, you can copy the table and amend it to deal with tasks instead by putting Task names in the first column, then referring to $B8:$B13,...etc instead of $E8:$E13,...etc.

The formulae assuming a one-hour lunchbreak will only give the correct number of hours if the staff always come in before lunch and leave after lunch. If you want to deal with staff working only a morning or only an afternoon, you can check that the start time is in the morning (up to 12noon) and the end time in the afternoon (1pm onwards) by doing this:
Replace

(SUM(INDEX($D$8:$D$13,MATCH($AJ7,$E$8:$E$13,0))-INDEX($C$8:$C$13,MATCH($AJ7,$E$8:$E$13,0)))*24)-1

with

IF(AND(INDEX($D$8:$D$13,MATCH($AJ7,$E$8:$E$13,0))>=(13/24),INDEX($C$8:$C$13,MATCH($AJ7,$E$8:$E$13,0))<=0.5),(SUM(INDEX($D$8:$D$13,MATCH($AJ7,$E$8:$E$13,0))-INDEX($C$8:$C$13,MATCH($AJ7,$E$8:$E$13,0)))*24)-1,SUM(INDEX($D$8:$D$13,MATCH($AJ7,$E$8:$E$13,0))-INDEX($C$8:$C$13,MATCH($AJ7,$E$8:$E$13,0)))*24)

This is for the first formula in the Names/Days table (Helen/Mon 1) - you'd need to copy/amend it into the rest of the table. If you're content that this won't happen, then don't worry about it.

Finally, a few 'worksheet management' points:
(1) You had six rows for positions/names in the first week, but only five for the rest of the month - I've inserted extra rows to make this five throughout (it made the find/replace easier so I hope you don't mind).
(2) The small labels in blue italics are Range Names which helped me remember what was named and how it was named - feel free to delete these.
(3) I've put borders round the named ranges as well, so that I could see where they started and ended - again, feel free to get rid of these borders.


Lastly - here's the file: RosterCal2 monthly hours totalled.xlsx

I hope that's all helpful.


Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[lizzardking]

Thanks for the detailed reply and solved workbook.
Cant thank you enough!
I am currently working through your methodology and explanation.
This may take a few days to understand fully, so, please be patient for my full reply.
N

[lizzardking]

Well,

I cant thank you enough!

But Ive massacred the xls as my manager has made me do things I thought I diddnt have to do.
Quick, dirty adjustments.
But its working

First. weve changed our minds to weekly hour summaries.
Secondly, we started a task calculation in the same way,. Then decided we diddnt need it.
Then we realised lunch hours for saturday and for hygine was 0,, so the calculation got adjusted.

Then I realised, if we change our workforce things go kind of wrong... Im keen to fix that... Im sure its something ive done.

Id rather get the Times written as 8AM and 8:30AM but I dont think thats possible.

Well, its working.
Im very happy.
Its gonna save us allot of time.
If you can suggest any more, its a bonus.

Kind regards

Neil



AUGUST ROSTER.xlsx

[Aardigspook]

Glad I could be of some help.

Regarding the times, it is possible...

Format your time cells using this Custom format:
h:mm\ AM/PM
then go to Conditional Formatting and create a new rule, selecting the option to 'Use a formula to determine which cells to format'. Now enter this formula:
=MINUTE(H55)=0
(H55 is the first cell in your list of times - replace it with the cell you're formatting, or the first cell in the range you've got selected).
Now click 'Format' and select the 'Number' tab then enter this custom number format:
h\ AM/PM

Voila - 8 am will display as 8 AM and 8.30 will display as 8:30 AM.

The attached file has this format applied to all the time cells in your calendar and the time list below (I also centred all the times, as I think that makes them easier to read).

If you need any help with the other parts of the worksheet again, then please get back to us - as you've marked this thread as 'solved' , you'll probably be best to start another thread, but please then refer back to this one so the contributors have some more context.

Best regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[lizzardking]

Hi Aardigspook

Thanks for crunching the time format issue.

Although, I think you forgot to attach the xls file.

Ill post some more queries in the forum as separate questions.

regards

Neil

[Aardigspook]

I think you forgot to attach the xls file.

oops

Here it is: August Roster 8am_830am times.xlsx

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.