Add Hours by Looping through Data Q

[Seanie]

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

 

[Peter T]

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

 

[Peter T]

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

 

[GS]

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?

 

[Peter T]

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

 

[GS]

It happens that Peter T formulated :

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(ThisDate)),
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

 

[Peter T]

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

It happens that Peter T formulated :

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(ThisDate)),
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

 

[GS]

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.