Overtime payroll sheet nightmare!

[Richskelly]

Hi I am trying to create an overtime sheet for my Hospital's salaries department and I am stuck!

They need to have an out put of the exact hours worked (eg 09:00-17:00) for each day.
Some people my work onsite in the hospital on several occasions per day so the exact hours for these need to be recorded (eg 09:00-17:00 and 19:00-22:00).

The time sheet makes us fill out each hour with an "x" for on site and an "o" for off site in a row of 24 boxes per day representing each hour of that day.

So what I need is, if there are 2 or 3 periods of onsite work per day (represented by a row of x's). can someone give me a formula to convert these to individual time periods in a box at the end of the row (eg 09:00-17:00, 19:00-22:00 and 23:00-00:00)

We currently have a time sheet which cant change in its appearance very much or they wont accept it.

PLEASE CAN SOMEONE HELP ME!!!!

 

[offthelip]

What about using

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64, align: right"]=COUNTIF(A2:X2,"x")[/TD]
[/TR]
</tbody>[/TABLE]

 

[Richskelly]

Thanks for your reply,
I've done that to add up the hours but I need to produce the range of exact times of coming in and going out (e.g. 09:00-17:00) and I need to replicate this for each time someone comes in over the course of the day.

I'd really appreciate your help

 

[Richskelly]

Can I send you what ice fine so far, it'll make more sense that way! It's cool if you can't accept!

 

[offthelip]

This UDF will do what you want but i had to make some assumption,
1:I assumed that the array where the x are are in columnn A to X
2: I assumed that the heading for each hour are in Row 1 and that they are TEXT not times. ( if this isn't the case you will just get numbers instead of times0

Function hrs(arr As Range)

inarr = arr
inwork = False
hrs = ""
fst = ""
For i = 1 To UBound(inarr, 2)
  If inarr(1, i) = "x" And Not (inwork) Then
   inwork = True
   hrs = hrs & fst & Cells(1, i)
   fst = " , "
  End If
  If inarr(1, i) <> "x" And (inwork) Then
   inwork = False
   hrs = hrs & "-" & Cells(1, i) & " "
  End If
Next i
If inwrk Then
   hrs = hrs & "-" & Cells(1, 24) & " "
End If
End Function

 

[Richskelly]

Thank you so much for taking the time, I really appreciate your time and help. I never knew that a UDF even existed.
So, looking at that UDF I have no idea how to augment it to suit my workbook. When I put it in as a UDF it just came up with the #NAME ? error

The times are all in as times and not TEXT

I have pasted in the times and first day which I set as 24th Dec below. Taking the first column, the 00:00 time(located in cell c3) is the start of the hour and the 01:00(located in cell c5) is the end of that hour. The "o" in cell c6 shows off site for that hour. This repeats itself in row 6 from c to z

Another layer of complexity is that when someone comes in on the half hour I have noted this as either 1/2x if they werent on off site call before ore after or 1/2x+o, as you can see in the hour 08:00-09:00 and again in the 17:00-18:00 cell below, if they are coming in or leaving the hospital on the half hour while on off site call.

I have added into this line a mock up of the calculations i need in the last 6 columns on far right under "On Site hours" and "Off Site Hours"

Calculating the on site hours is more important than the off site hours as these could be easily figured out
[TABLE="width: 1747"]
<colgroup><col><col><col span="24"><col><col><col><col><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]02:00[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]04:00[/TD]
[TD="align: right"]05:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]23:00[/TD]
[TD]Hours Worked On Site[/TD]
[TD]Total On Site hours[/TD]
[TD]Hours On Call Off Site[/TD]
[TD]Total Off Site hours[/TD]
[TD]On Site Times[/TD]
[TD]On Site Times[/TD]
[TD]On Site Times[/TD]
[TD]Off Site Times[/TD]
[TD]Off Site Times[/TD]
[TD]Off Site Times[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Day[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]02:00[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]04:00[/TD]
[TD="align: right"]05:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]00:00[/TD]
[/TR]
[TR]
[TD]24th Dec[/TD]
[TD]Mon[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]1/2x+o[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]1/2x+o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD]o[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD]08:30-17:30[/TD]
[TD]20:00-23:00[/TD]
[TD] [/TD]
[TD]00:00-08:30[/TD]
[TD]17:30-21:00[/TD]
[TD]23:00-00:00[/TD]
[/TR]
</tbody>[/TABLE]



Thank you so much again for your help. If I can send you the workbook, please tell me, it might make this much easier as I'm completely new to excel

 

[offthelip]

This is a completely different problem compared to your original post. the four columns at the end bear no resemblance to the display you originally asked for e.g (09:00-17:00 and 19:00-22:00) .
My original response suggesting countif will sum the hours worked on site for you, although you will have to deal with the really odd format you have decide to use for half hours. What is the difference between hours worked on site and total hours on site?
How are you doing the hours on call off site.
This is all a bad case of MISSION CREEP, and I suspect you still haven't told me the whole story.
I am sorry but I can't spend any more time on this one.