I posted this in the Functions Group but it didn't progress to a
solution, I wonder is it too complicated for a function and perhaps
VBA could be a solution

Basically I am trying to add up employee's clocking for each time
segment of the day. A time segement = 1 hour eg 11:00am-12:00pm;
12:00pm-13:00pm etc etc. I have all my data in columns, each row
represent and entry for the day in question, for an employee (note I
could have several days data), so is it possible to loop through my
rows to add the total hours worked in a stated date for all employees
in each Time segment and return this value in a certain cell in my
sheet? The Date I would get from a cell I have already populated

As an example, the following would be some clocks
Emp#1 In=09:30 Out=14:30 Date=22/06/2011
Emp#2 In=10:30 Out=14:55 Date=22/06/2011
Emp#3 In= 11:30 Out=15:30 Date=22/06/2011
Emp#1 In= 16:30 Out=20:30 Date=23/06/2011
Emp#1 In= 07:30 Out=12:30 Date=24/06/2011


So for the following Hour segments on 22/06/2011 the code should
return:-

09:00-10:00 = 30mins labour hours worked
10:01-11:00 = 90mins labour hours worked
11:01-12:00 = 150min labour hours worked
12:01-13:00 = 180min labour hours worked
13:01-14:00 = 180min labour hours worked
14:01-15:00 = 145min labour hours worked
15:01-16:00 = 30min labour hours worked

Hi
How exactly is your data laid out?
1. Do you have an Employee column, In column, Out column and Date
column?
2. Is your data in one block with no empty rows?

If you have 1 and 2 above then a Pivot Table might do the job you
want. They are designed to group and count/total data.

Say exactly what your data looks like & we can go through setting up a
Pivot Table (if applicable).
regards
Paul


On Jun 22, 7:32*am, Seanie wrote:
I posted this in the Functions Group but it didn't progress to a
solution, I wonder is it too complicated for a function and perhaps
VBA could be a solution

Basically I am trying to add up employee's clocking for each time
segment of the day. A time segement = 1 hour eg 11:00am-12:00pm;
12:00pm-13:00pm etc etc. I have all my data in columns, each row
represent and entry for the day in question, for an employee (note I
could have several days data), so is it possible to loop through my
rows to add the total hours worked in a stated date for all employees
in each Time segment and return this value in a certain cell in my
sheet? The Date I would get from a cell I have already populated

As an example, the following would be some clocks
Emp#1 In=09:30 Out=14:30 Date=22/06/2011
Emp#2 In=10:30 Out=14:55 Date=22/06/2011
Emp#3 In= 11:30 Out=15:30 Date=22/06/2011
Emp#1 In= 16:30 Out=20:30 Date=23/06/2011
Emp#1 In= 07:30 Out=12:30 Date=24/06/2011

So for the following Hour segments on 22/06/2011 the code should
return:-

09:00-10:00 = 30mins labour hours worked
10:01-11:00 = 90mins labour hours worked
11:01-12:00 = 150min labour hours worked
12:01-13:00 = 180min labour hours worked
13:01-14:00 = 180min labour hours worked
14:01-15:00 = 145min labour hours worked
15:01-16:00 = 30min labour hours worked

Paul thanks for your reply, yes my data is laid out as you describe,
but its a dynamic table
ColA=Employee No
ColB=Date the employee worked (1 row per date worked)
ColC=ClockIn Time
ColD=ClockOut Time

So a couple of Rows of data might look like:-

98 - 22/06/2011 - 11:35 - 18:30
147 - 22/06/2011 - 07:48 - 15:35

So in time segment:-
07:00-08:30 answer I'm looking for is 12 mins
11:00-12:00 answer I'm looking for is 120 mins
etc etc covering all other hours for the day

I have Breaks etc which I will want incoroprated but if I can cross
over the first hurdle first would be great

On Jun 22, 5:32*am, Seanie wrote:
Paul thanks for your reply, yes my data is laid out as you describe,
but its a dynamic table
ColA=Employee No
ColB=Date the employee worked (1 row per date worked)
ColC=ClockIn Time
ColD=ClockOut Time

So a couple of Rows of data might look like:-

98 - 22/06/2011 - 11:35 - 18:30
147 - 22/06/2011 - 07:48 - 15:35

So in time segment:-
07:00-08:30 answer I'm looking for is 12 mins
11:00-12:00 answer I'm looking for is 120 mins
etc etc covering all other hours for the day

I have Breaks etc which I will want incoroprated but if I can cross
over the first hurdle first would be great

I thought I answered this. Enter this and copy down
'=SUMPRODUCT((HOUR($F$4:$F$300)=ROW(A1))*1)

On Jun 22, 4:09*pm, Don Guillett wrote:
On Jun 22, 5:32*am, Seanie wrote:





Paul thanks for your reply, yes my data is laid out as you describe,
but its a dynamic table
ColA=Employee No
ColB=Date the employee worked (1 row per date worked)
ColC=ClockIn Time
ColD=ClockOut Time

So a couple of Rows of data might look like:-

98 - 22/06/2011 - 11:35 - 18:30
147 - 22/06/2011 - 07:48 - 15:35

So in time segment:-
07:00-08:30 answer I'm looking for is 12 mins
11:00-12:00 answer I'm looking for is 120 mins
etc etc covering all other hours for the day

I have Breaks etc which I will want incoroprated but if I can cross
over the first hurdle first would be great

I thought I answered this. Enter this and copy down
'=SUMPRODUCT((HOUR($F$4:$F$300)=ROW(A1))*1)- Hide quoted text -

- Show quoted text -

How exactly does this work?

On Jun 22, 10:59*am, Seanie wrote:
On Jun 22, 4:09*pm, Don Guillett wrote:









On Jun 22, 5:32*am, Seanie wrote:

Paul thanks for your reply, yes my data is laid out as you describe,
but its a dynamic table
ColA=Employee No
ColB=Date the employee worked (1 row per date worked)
ColC=ClockIn Time
ColD=ClockOut Time

So a couple of Rows of data might look like:-

98 - 22/06/2011 - 11:35 - 18:30
147 - 22/06/2011 - 07:48 - 15:35

So in time segment:-
07:00-08:30 answer I'm looking for is 12 mins
11:00-12:00 answer I'm looking for is 120 mins
etc etc covering all other hours for the day

I have Breaks etc which I will want incoroprated but if I can cross
over the first hurdle first would be great

I thought I answered this. Enter this and copy down
'=SUMPRODUCT((HOUR($F$4:$F$300)=ROW(A1))*1)- Hide quoted text -

- Show quoted text -

How exactly does this work?

Just copy the formula into any cell and then copy down 24 rows with
the fill handle, Didn't you send me a file to dguillett1 @gmail.com

Thanks Don, nope didn't send a file to you

I can see how it works now, only thing is, if 2 employees clockin
during an hour, your function will return 2, I'm after the total hours
worked for that hour, not the number of employees that have clocked in

Seanie used his keyboard to write :
Thanks Don, nope didn't send a file to you

I can see how it works now, only thing is, if 2 employees clockin
during an hour, your function will return 2, I'm after the total hours
worked for that hour, not the number of employees that have clocked in

And so if 2 employees clock in for 1 hour (each), is that not 2 hours
worked?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Spot on Garry, but if 1 of the employees clocks in at 9:10 and the
other at 9:30, thats a total of 80mins for the 9:00-10:00 hour, which
is the answer in that scenario I'm looking for

Seanie was thinking very hard :
Spot on Garry, but if 1 of the employees clocks in at 9:10 and the
other at 9:30, thats a total of 80mins for the 9:00-10:00 hour, which
is the answer in that scenario I'm looking for

Ok, details are helful. It just seemed simple given what you stated
without elaborating. I do various projects for clients that monitor
elapsed time by project, regardless of who works on it. Thus, I
understand ( and appreciate) your attention to the finer details in
context to what you're trying to do.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Following returns your anticipated results but needs more testing

Put your sample data in A2:C6
colA: date
colB: time In
colC: time out

If the times ever span midnight would need to adapt

Sub test()
Dim i As Long, j As Long, h As Long
Dim hIn As Single, hOut As Single
Dim mnIn As Single, mnOut As Single
Dim dFirst As Date, dLast As Date
Dim dIn As Date, dOut As Date
Dim aHour() As Date, aMins() As Long
Dim rng As Range

' in real life use a sunction to get dFirst & dLast
' ie the earlest & lates times
dFirst = #6/22/2011 8:00:00 AM#
dLast = #6/24/2011 1:00:00 PM#

' ditto about getting the source range
Set rng = Range("A2:C6")

h = (dLast - dFirst) * 24
ReDim aHour(1 To h + 1, 1 To 2)
ReDim aMins(1 To h, 1 To 1) As Long

aHour(1, 1) = dFirst
For i = 1 To h
aHour(i, 2) = aHour(i, 1) + 1 / 24
aHour(i + 1, 1) = aHour(i, 2)
Next


For i = 1 To rng.Rows.Count
dIn = rng(i, 1) + rng(i, 2)
dOut = rng(i, 1) + rng(i, 3)

hIn = (dIn - dFirst) * 24 + 1
mnIn = hIn - Int(hIn)
hIn = Int(hIn)

hOut = (dOut - dFirst) * 24 + 1
mnOut = hOut - Int(hOut)
hOut = Int(hOut)

If mnIn Then
aMins(hIn, 1) = aMins(hIn, 1) + 60 - (mnIn * 60)
hIn = hIn + 1
End If
If mnOut Then
aMins(hOut, 1) = aMins(hOut, 1) + (mnOut * 60)
hOut = hOut - 1
End If

For j = hIn To hOut
aMins(j, 1) = aMins(j, 1) + 60
Next

Next

With Range("F1:G" & h)
.Value = aHour
.NumberFormat = "hh:mm"
End With

Range("h1:H" & h).Value = aMins

End Sub


Lots of ways to reduce the unwanted hours with zero minutes, say over
evenings / WE. Eg only dump hours with non-zero minutes.


Regards,
Peter T




"Seanie" wrote in message
...
I posted this in the Functions Group but it didn't progress to a
solution, I wonder is it too complicated for a function and perhaps
VBA could be a solution

Basically I am trying to add up employee's clocking for each time
segment of the day. A time segement = 1 hour eg 11:00am-12:00pm;
12:00pm-13:00pm etc etc. I have all my data in columns, each row
represent and entry for the day in question, for an employee (note I
could have several days data), so is it possible to loop through my
rows to add the total hours worked in a stated date for all employees
in each Time segment and return this value in a certain cell in my
sheet? The Date I would get from a cell I have already populated

As an example, the following would be some clocks
Emp#1 In=09:30 Out=14:30 Date=22/06/2011
Emp#2 In=10:30 Out=14:55 Date=22/06/2011
Emp#3 In= 11:30 Out=15:30 Date=22/06/2011
Emp#1 In= 16:30 Out=20:30 Date=23/06/2011
Emp#1 In= 07:30 Out=12:30 Date=24/06/2011


So for the following Hour segments on 22/06/2011 the code should
return:-

09:00-10:00 = 30mins labour hours worked
10:01-11:00 = 90mins labour hours worked
11:01-12:00 = 150min labour hours worked
12:01-13:00 = 180min labour hours worked
13:01-14:00 = 180min labour hours worked
14:01-15:00 = 145min labour hours worked
15:01-16:00 = 30min labour hours worked

^^^ Thanks for the code

It debugs on aMins(hIn, 1) = aMins(hIn, 1) + 60 - (mnIn * 60), with
Message "subscript out of range"

As you detail my data is in A2:C6

It's still working fine for me, at least with your original test data. The
error suggests the value hIn is either 0 or above the ubound of the array.
Add these debugs after the original code lines as indicated

h = (dLast - dFirst) * 24
Debug.Print "h: "; h

hIn = Int(hIn)
Debug.Print i, "hIn: "; hIn

I get the following in the Immediate window (ctrl-g) with your data

h: 53
1 hIn: 2
2 hIn: 3
3 hIn: 4
4 hIn: 33
5 hIn: 48

Post your results

Regards,
Peter T

To clarify, the dates and times should be date values (below in my date
format)

in A2:C6
22-06-2011 09:30 14:30
22-06-2011 10:00 14:55
22-06-2011 11:30 15:30
23-06-2011 16:30 20:30
24-06-2011 07:30 12:30

the actual values
40716 0.395833333 0.604166667
40716 0.416666667 0.621527778
40716 0.479166667 0.645833333
40717 0.6875 0.854166667
40718 0.3125 0.520833333

Do text-to-columns (space delim) and date format as required

Peter T



"Peter T" wrote in message
...
It's still working fine for me, at least with your original test data. The
error suggests the value hIn is either 0 or above the ubound of the array.
Add these debugs after the original code lines as indicated

h = (dLast - dFirst) * 24
Debug.Print "h: "; h

hIn = Int(hIn)
Debug.Print i, "hIn: "; hIn

I get the following in the Immediate window (ctrl-g) with your data

h: 53
1 hIn: 2
2 hIn: 3
3 hIn: 4
4 hIn: 33
5 hIn: 48

Post your results

Regards,
Peter T

Nope, still getting the Debug on same line (and nothing prints). My
Data in A2:C6 is

22/06/11 11:00 16:00
22/06/11 07:30 15:00
22/06/11 09:30 17:00
22/06/11 09:05 14:20
22/06/11 11:30 18:20


And the Code you have given + debug print above

Sub test()
Dim i As Long, j As Long, h As Long
Dim hIn As Single, hOut As Single
Dim mnIn As Single, mnOut As Single
Dim dFirst As Date, dLast As Date
Dim dIn As Date, dOut As Date
Dim aHour() As Date, aMins() As Long
Dim rng As Range


' in real life use a sunction to get dFirst & dLast
' ie the earlest & lates times
dFirst = #6/22/2011 8:00:00 AM#
dLast = #6/24/2011 1:00:00 PM#


' ditto about getting the source range
Set rng = Range("A2:C6")


h = (dLast - dFirst) * 24
Debug.Print "h: "; h
ReDim aHour(1 To h + 1, 1 To 2)
ReDim aMins(1 To h, 1 To 1) As Long


aHour(1, 1) = dFirst
For i = 1 To h
aHour(i, 2) = aHour(i, 1) + 1 / 24
aHour(i + 1, 1) = aHour(i, 2)
Next


For i = 1 To rng.Rows.Count
dIn = rng(i, 1) + rng(i, 2)
dOut = rng(i, 1) + rng(i, 3)


hIn = (dIn - dFirst) * 24 + 1
mnIn = hIn - Int(hIn)
hIn = Int(hIn)
Debug.Print i, "hIn: "; hIn



hOut = (dOut - dFirst) * 24 + 1
mnOut = hOut - Int(hOut)
hOut = Int(hOut)


If mnIn Then
aMins(hIn, 1) = aMins(hIn, 1) + 60 - (mnIn * 60)
hIn = hIn + 1
End If
If mnOut Then
aMins(hOut, 1) = aMins(hOut, 1) + (mnOut * 60)
hOut = hOut - 1
End If


For j = hIn To hOut
aMins(j, 1) = aMins(j, 1) + 60
Next


Next


With Range("F1:G" & h)
.Value = aHour
.NumberFormat = "hh:mm"
End With


Range("h1:H" & h).Value = aMins


End Sub

On Jun 23, 10:21*am, "Peter T" wrote:
To clarify, the dates and times should be date values (below in my date
format)

in A2:C6
22-06-2011 09:30 14:30
22-06-2011 10:00 14:55
22-06-2011 11:30 15:30
23-06-2011 16:30 20:30
24-06-2011 07:30 12:30

the actual values
40716 0.395833333 0.604166667
40716 0.416666667 0.621527778
40716 0.479166667 0.645833333
40717 0.6875 0.854166667
40718 0.3125 0.520833333

Do text-to-columns (space delim) and date format as required

Peter T

"Peter T" wrote in message

...



It's still working fine for me, at least with your original test data. The
error suggests the value hIn is either 0 or above the ubound of the array.
Add these debugs after the original code lines as indicated

* *h = (dLast - dFirst) * 24
Debug.Print "h: "; h

* * * *hIn = Int(hIn)
Debug.Print i, "hIn: "; hIn

I get the following in the Immediate window (ctrl-g) with your data

h: *53
1 * * * * * *hIn: *2
2 * * * * * *hIn: *3
3 * * * * * *hIn: *4
4 * * * * * *hIn: *33
5 * * * * * *hIn: *48

Post your results

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Yes my actual values are the same as ^^^

40716 0.395833333 0.604166667
40716 0.416666667 0.621527778
40716 0.479166667 0.645833333
40716 0.6875 0.854166667
40716 0.3125 0.520833333

You say "Nothing prints", but you must surely get at least the 1st debug and
the 2nd debug line must print at least once (in the first pass of the loop)

When the code breaks, in the immediate window manually debug the following
(paste each individually and hit enter)

?h
?i
?hin

what do you get

Regards,
Peter T

Peter, bit lost here, but on 1st debug line at:- aMins(hIn, 1) =
aMins(hIn, 1) + 60 - (mnIn * 60) the following appears in the
immediate window, when I delete the contents shown and type ?h

?h
53

Then -

?i
5

Then -

?hin
0

Hope that makes sense, not sure what these figures represent

h=53 is the number of hours between the dFirst and dLast times, and the size
the arrays have been dimensioned to.

i=5 means things are failing on the last loop while looking at the 5 rows of
data, ie looking at row 6

hin=0 ' (it should be 48)
is the result of
dIn = A6+B6 ' 5th loop means data in row 6
hIn = dIn-dFirst

So, what values do you have in A6 & B6? Are you sure the first row of data
is in row2 and not perhaps in row1


Regards,
Peter T



"Seanie" wrote in message
...
Peter, bit lost here, but on 1st debug line at:- aMins(hIn, 1) =
aMins(hIn, 1) + 60 - (mnIn * 60) the following appears in the
immediate window, when I delete the contents shown and type ?h

?h
53

Then -

?i
5

Then -

?hin
0

Hope that makes sense, not sure what these figures represent

A6 = 22/06/2011
B6 = 07:30

Has it got to do with the fact that Row6 has a start time earlier than
Row1? Real life, that could be the case, as Data would be sorted by
Emplee No, and Row6 Employee might be # 100, whereas Row2 might be #15
etc etc

Peter, spot on, works great, exactly (I'll need some mods for real
life data) what I'm after

A couple of tweaks:-

1) To be able to run based on a "date" value. In my example I have
only 22/06/2011, but there would be multiple dates in my data (the
data would be sorted by emply # by date). The date value to run
against would be taken from A6 on Sheet2, so I would run different
code for each "date"

2) I have also Breaks data (out/in), I will need to subtract these in
the same way as I added the Clockin/Outs etc. These breaks are in ColE
& ColF

3) On the Output, I only need the column that shows the total Minutes

Very clever good I must say, you put a lot of effort in

According to your sample data A6 should be 24/06/2011 (not 22nd) try that.

Reason it failed is because because the TimeIn is before the dFirst time
(see top of the code). Alternatively you could leave as is and change
dFirst = #6/22/2011 8:00:00 AM#
to
dFirst = #6/22/2011 7:00:00 AM#

However you won't get the anticipated results in your OP of course.

Apart from the above the entries can be in any order, providing alltimes are
between dFirst and dLast

Regards,
Peter T

"Seanie" wrote in message

Peter, spot on, works great, exactly (I'll need some mods for real
life data) what I'm after

So you're saying reverting to your original sample data fixed it?

A couple of tweaks:-

1) To be able to run based on a "date" value. In my example I have
only 22/06/2011, but there would be multiple dates in my data (the
data would be sorted by emply # by date). The date value to run
against would be taken from A6 on Sheet2, so I would run different
code for each "date"

As written, an array of hour slots is defined between the earlierst and
latest times, then minutes added possibly repeatedly to requisite "slots"
during the loop. One way to define these get the min/max times from the
source data, eg

Set rng = Range("A2:C6")
With Application.WorksheetFunction
dFirst = .Min(rng.Columns(2).Value2)
dFirst = TimeSerial(Hour(dFirst), 0, 0)
dFirst = dFirst + .Min(rng.Columns(1).Value2)
dLast = .Max(rng.Columns(3).Value2)
dLast = TimeSerial(Hour(dLast) + 1, 0, 0)
dLast = dLast + .Max(rng.Columns(1))
End With

Obiously you can define or get dFirst & dLast from wherever you want as long
as they embrace the entire set of times.

As I mentioned, no need for your In-Time's and dates to be sorted, though of
course you can if you want


2) I have also Breaks data (out/in), I will need to subtract these in
the same way as I added the Clockin/Outs etc. These breaks are in ColE
& ColF

You can add more ranges, in this case instead of adding the times to each
time slot, subtract them. The code would need adapting slightly, eg split
into intrinsic functions.


3) On the Output, I only need the column that shows the total Minutes

That should be simple to adapt, look at aHour() which is only added for
display purposes.

Try and work out what's going on in the routine. At first it might seem
complicated but it's all quite logical. Then it should be much easier to
adapt to as required.

Very clever good I must say, you put a lot of effort in

I'm sure there several approaches, this is the first that came to mind. I'm
almost sure it'd be possible entirely in Excel though would take
significantly longer (for me) to work out how.

Regards,
Peter T

Seanie formulated on Thursday :
Peter, spot on, works great, exactly (I'll need some mods for real
life data) what I'm after

A couple of tweaks:-

1) To be able to run based on a "date" value. In my example I have
only 22/06/2011, but there would be multiple dates in my data (the
data would be sorted by emply # by date). The date value to run
against would be taken from A6 on Sheet2, so I would run different
code for each "date"

2) I have also Breaks data (out/in), I will need to subtract these in
the same way as I added the Clockin/Outs etc. These breaks are in ColE
& ColF

3) On the Output, I only need the column that shows the total Minutes

Very clever good I must say, you put a lot of effort in

I usually grab the total elapsed time worked for a specific period
(day,week,month) using an in cell formula. This requires using defined
name refs to date cells and start/stop times (or total elapsed time)
per row of input data. The result is a column each for ElapsedTime,
PeriodTotal, [and ProjectTotal if tracking by project]. All is done via
in cell formulas.

Is there any reason why you can't do the same?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message

I usually grab the total elapsed time worked for a specific period
(day,week,month) using an in cell formula. This requires using defined
name refs to date cells and start/stop times (or total elapsed time) per
row of input data. The result is a column each for ElapsedTime,
PeriodTotal, [and ProjectTotal if tracking by project]. All is done via in
cell formulas.

Is there any reason why you can't do the same?

OK, how would you do it, eg referring to the sample & output required as
given in the OP (no doubt the input split into a few columns as I did in the
VBA demo).

As I mentioned in adjacent post I'm sure it's possible, but pleased to see
someone else work out how :-)

Regards,
Peter T

It happens that Peter T formulated :
"GS" wrote in message

I usually grab the total elapsed time worked for a specific period
(day,week,month) using an in cell formula. This requires using defined name
refs to date cells and start/stop times (or total elapsed time) per row of
input data. The result is a column each for ElapsedTime, PeriodTotal, [and
ProjectTotal if tracking by project]. All is done via in cell formulas.

Is there any reason why you can't do the same?

OK, how would you do it, eg referring to the sample & output required as
given in the OP (no doubt the input split into a few columns as I did in the
VBA demo).

As I mentioned in adjacent post I'm sure it's possible, but pleased to see
someone else work out how :-)

Regards,
Peter T

Peter,
It depends on the approach, AND how the spreadsheet is designed. For
example, I have a template I use that's laid out as follows:

ColA: left empty to place checkmark when invoiced
ColB: DateWorked (Enter date work period starts)
ColC: Month (Only used to display by billing period)
Formula: =ThisDate Format: "mmm-yy"
ColD: Project (Only used to bill by project or sub-project)
ColE: ServiceItem (being billed)
ColF: Start (Time work started)
ColG: Stop (Time work stopped)
ColH: ElapsedTime (Contains the following formula)
=IF(AND(Start<"",Stop<""),ROUND(MOD(Stop-Start,1)*24,2),"")
**This formula accomodates shifts that cross midnight**
**Calcs hours to 2 decimal places**

ColI: PeriodTotal (Month in this case;
contains the following formula)

=IF(AND(NextDate="",This_ET<""),
ProjectTime-SUM($I$19:LastCell),
IF(AND(NextDate<"",Next_ET="",
MONTH(ThisDate)=MONTH(NextDate)),
ProjectTime-SUM($I$19:LastCell),
IF(AND(NextDate<"",Next_ET<"",
MONTH(ThisDate)=MONTH(NextDate)),"",
IF(AND(NextDate<"",MONTH(NextDate)<MONTH(ThisDat e)),
ProjectTime-SUM($I$19:LastCell),""))))
**This formula determines the period (day,month)**
**Only displays total for the period, thus periods must be grouped**
**Absolute ref to $I$19 is an empty row where this sub-project time
record starts**

ColJ: ProjectTotal (Contains the following formula)
=IF(This_ET<"",LastCell+This_ET,"")

Defined Names used: (all have local scope; 'n' refs ActiveCell)
Start
ColAbsolute (F), RowRelative (n); RefersTo: =$Fn

Stop
ColAbsolute (G), RowRelative (n); RefersTo: =$Gn

This_ET
ColAbsolute (H), RowRelative (n); RefersTo: =$Hn

ThisDate
ColAbsolute (B), RowRelative (n); RefersTo: =$Bn

Next_ET
ColAbsolute (H), RowRelative (n+1); RefersTo: =$Hn+1

NextDate
ColAbsolute (B), RowRelative (n+1); RefersTo: =$Bn+1

ProjectTime
ColAbsolute (J), RowRelative (n); RefersTo: =$Jn

LastCell
FullyRelative [Cells(n-1,n)]

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks but don't see how this approach can be adapted to the OP's task.
Unless I'm missing something which is always possible!

Regards,
Peter T



"GS" wrote in message ...
It happens that Peter T formulated :
"GS" wrote in message

I usually grab the total elapsed time worked for a specific period
(day,week,month) using an in cell formula. This requires using defined
name refs to date cells and start/stop times (or total elapsed time) per
row of input data. The result is a column each for ElapsedTime,
PeriodTotal, [and ProjectTotal if tracking by project]. All is done via
in cell formulas.

Is there any reason why you can't do the same?

OK, how would you do it, eg referring to the sample & output required as
given in the OP (no doubt the input split into a few columns as I did in
the VBA demo).

As I mentioned in adjacent post I'm sure it's possible, but pleased to
see someone else work out how :-)

Regards,
Peter T

Peter,
It depends on the approach, AND how the spreadsheet is designed. For
example, I have a template I use that's laid out as follows:

ColA: left empty to place checkmark when invoiced
ColB: DateWorked (Enter date work period starts)
ColC: Month (Only used to display by billing period)
Formula: =ThisDate Format: "mmm-yy"
ColD: Project (Only used to bill by project or sub-project)
ColE: ServiceItem (being billed)
ColF: Start (Time work started)
ColG: Stop (Time work stopped)
ColH: ElapsedTime (Contains the following formula)
=IF(AND(Start<"",Stop<""),ROUND(MOD(Stop-Start,1)*24,2),"")
**This formula accomodates shifts that cross midnight**
**Calcs hours to 2 decimal places**

ColI: PeriodTotal (Month in this case;
contains the following formula)

=IF(AND(NextDate="",This_ET<""),
ProjectTime-SUM($I$19:LastCell),
IF(AND(NextDate<"",Next_ET="",
MONTH(ThisDate)=MONTH(NextDate)),
ProjectTime-SUM($I$19:LastCell),
IF(AND(NextDate<"",Next_ET<"",
MONTH(ThisDate)=MONTH(NextDate)),"",
IF(AND(NextDate<"",MONTH(NextDate)<MONTH(ThisDat e)),
ProjectTime-SUM($I$19:LastCell),""))))
**This formula determines the period (day,month)**
**Only displays total for the period, thus periods must be grouped**
**Absolute ref to $I$19 is an empty row where this sub-project time record
starts**

ColJ: ProjectTotal (Contains the following formula)
=IF(This_ET<"",LastCell+This_ET,"")

Defined Names used: (all have local scope; 'n' refs ActiveCell)
Start
ColAbsolute (F), RowRelative (n); RefersTo: =$Fn

Stop
ColAbsolute (G), RowRelative (n); RefersTo: =$Gn

This_ET
ColAbsolute (H), RowRelative (n); RefersTo: =$Hn

ThisDate
ColAbsolute (B), RowRelative (n); RefersTo: =$Bn

Next_ET
ColAbsolute (H), RowRelative (n+1); RefersTo: =$Hn+1

NextDate
ColAbsolute (B), RowRelative (n+1); RefersTo: =$Bn+1

ProjectTime
ColAbsolute (J), RowRelative (n); RefersTo: =$Jn

LastCell
FullyRelative [Cells(n-1,n)]

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Peter T wrote on 6/23/2011 :
Thanks but don't see how this approach can be adapted to the OP's task.
Unless I'm missing something which is always possible!

Well I'm not sure it can be adapted if the OP's scenario isn't
structured similar to this concept. My thinking was that the
PeriodTotal formula could be revised to tally time periods rather than
days/months.

All I intended to do here is demo how this can be achieved on a row by
row basis for each entry for any given day. Of course, the demo shows
how it works for each entry for each day, for every day of any month.
The concept is the same but with different period totals.

Basically, each row checks to see if the next row falls within the same
time period. VBA could do this more easily than in cell formulas (in
this OP's scenario) because of the finer degree of the time periods.
And so I thought that presenting this concept would hel give ome ideas
toward a suitable solution.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc