It happens that Peter T formulated :

GS said:

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