Formulas

[Anonymous]

Hia all

I have a pivot based in a spreadsheet...

The spreadsheet has employee number, date and time the employee clocked in or out

The script to load data is:

Load

Funcionario,

Data,

Hora,

rangesum( 1, if(peek('Funcionario') = Funcionario and peek('Data') = Data, peek(Clockout))) as Clockout

FROM

(biff, embedded labels)

The "result" is:

My question is... how can I calculate the time worked in the morning and the afternoon

For instance...employee 20021... I would like to have a column between 2 and 3.... that would calculate 13:10 - 9:10... (column 2 minus column 1)... and get 04:00...etc...

Thanks

Hugo

[sunny_talwar]

Are you wanting to do this in the script?

[Anonymous]

In the script... or in the pivot...

Thanks

[Kushal_Chawda]

may be this link will help

Re: Working hours only

[Anonymous]

In the script... or in the pivot...

Thanks

[marcus_sommer]

Here is one opportunity how it could be solved - with a join approach to bring all to calulate data in one record and which in this case excludes uncompleted records. The rawdata are removed but this could be reversed if some fields would be renamed to avoid synthetic keys.

If you preferred another solution the for the join used counter-fields RecordPair and RecordDelimiter will be useful, too - for examples as flag within a set analysis.

- Marcus