Expression for Timesheet

Miranda_Lubarsk · 12-09-2019 12:07 PM

Hello all!

It’s that time of year when I am paying attention to our app and working out the kinks in our Timesheets form.

This past year, in our Timesheets, we have recorded our [Employee], and then a [Co-gardener] in two separate columns. [Duration] of the visit is recorded, and if a Co-gardener was recorded in the same row, then the [Duration] is doubled to come up with a [Combined Duration]. That is, the combined time spent at the garden.

However, I would like to be able to put up to 4 employees on a single Timesheet, and have the [Combined Duration] calculated from that. So there could either be 1, 2, 3 or 4 employees recorded on the timesheet, depending on the circumstance, and then depending on what is filled in, the Duration is doubled, tripled or quadrupled. Or just kept as is if it’s only one person.

My expression for doubling the [Duration] is:

IF(ISNOTBLANK([Co-gardener]), [Duration] + [Duration], [Duration])

This has worked and has been great!

So my question is, what kind of expression would I need to put together to accomplish the same thing with 4 possible employees instead of just two. Can I still use an If, then expression? Or would it be something else totally different?

Thanks so much!
Miranda

TyAlevizos

Hi,

I built a small app that hopefully helps to solve this and various similar problems. Why stop at 4 people, for example?

https://www.appsheet.com/samples/Sample-App?appGuidString=20638e07-8824-4d54-8f02-09a56500854c

This app has a timesheet which has timesheet lineitems

When you create a new timesheet you will auto-create a single line item for it (using an action and a workflow)

The line item lets you enter a name, or, the number of “people”. It also calculates the duration from a start and end time.

You can have multiple line items on each timesheet, although your original description doesn’t specify that.

And finally, the timesheet totals up all of its line items. So you could have, e.g., a line item that says “3 people worked for one hour” and a second line item that says “Miranda worked for 8 hours” - these will add up to 11 on the timesheet.

Hope this helps.

Miranda_Lubarsk

Hello & thank you for the response. We need to record the names of the employees who are at each garden so I can’t just list a number and have it total up the time with numbers only.

Can you help me find a solution that is more in line with what I already have? Similar to the expression that I have, but a way to apply if I list up to 4 people?

Many thanks.

TyAlevizos

Hi, no worries, I was keying off your last sentence “Or would it be something else totally different?” - but I’m also the poster child for over thinking things, which I did.

Assuming you have four columns for four people, and a fifth column for duration (which I made a simple number), and you want to multiply, it could look like this:

[Duration] * 
(
IF(ISNOTBLANK([Person1]), 1,0) +
IF(ISNOTBLANK([Person2]), 1,0) +
IF(ISNOTBLANK([Person3]), 1,0) +
IF(ISNOTBLANK([Person4]), 1,0)
)

there’s many permutations or different things you could do here.

Miranda_Lubarsk

I tried:

[Duration] * (IF(ISNOTBLANK([Employee Name]),1,0) + IF(ISNOTBLANK([Co-gardener]),1,0) + IF(ISNOTBLANK([Co-gardener 2]),1,0), + IF(ISNOTBLANK([Co-gardener 3]),1,0))

However, it says there is an invalid operator. Not sure what that means?

Steve

Extra comma:

Miranda_Lubarsk

Thanks!

Here is a screen shot of a new problem…

Steve

The expression shown in the screenshot is not the one you posted previously: there is no multiplication done. But that doesn’t matter because you aren’t allowed to multiply Duration values. A different approach:

(
  IFS(ISNOTBLANK([Person1]), [Duration])
  + IFS(ISNOTBLANK([Person2]), [Duration])
  + IFS(ISNOTBLANK([Person3]), [Duration])
  + IFS(ISNOTBLANK([Person4]), [Duration])
)

Miranda_Lubarsk

Thank you Steve!! Stellar!