Hi WF Community, I'm trying to create a calculated custom column in a Timesheet report based on the following: If totalHours is between 41-43 hours, multiple these additional hours by a factor of 1If totalHours is greater than 43 hours, multiple these additional hours by a factor of 1.5 Examples:If Joe Smith logged 43 hours this week, the calculated custom column would display a value of 3 (3 hours*1)If Joe Smith logged 46 hours this week, the calculated custom column would display a value of 4.5 (3 hours*1.5) Here's what I have for the custom column (text mode), but it's not returning any values: displayname=Calculated Lieu Timelinkedname=directnamekey=totalHoursquerysort=totalHourstextmode=truevalueexpression=IF({totalHours}>40,({totalHours}-40)*1),IF({totalHours}>43,({totalHours}-40)*1.5,"")valueformat=int Does anyone know where I might be going astray?Thanks.Nick

Hi Nick, Try the below code instead: displayname=Calculated Lieu Timelinkedname=directnamekey=totalHoursquerysort=totalHourstextmode=truevalueexpression=IF({totalHours}>43,PROD(SUB({totalHours},40),1.5),IF({totalHours}>40,PROD(SUB({totalHours},40),1)))valueformat=double A couple of pointers for you: Since the result of your value expression could return values that have a decimal place (because you are multiplying by 1.5), use the double valueformat. Integers will only return whole numbers.On your existing value expression, you are checking to see if the total hours is > 40 first. This condition will always be met if the user enters a value greater than 43 and so will multiply by 1. I've reordered your expression so that we check for values >43 first, that way if a user enters 42, the first condition will fail and Workfront will then check for values >40. Best Regards,Rich.

N

NickVa7

Level 9

Solved

Lieu Hours Calculation in Custom Report Column

Forum|Forum|4 years ago
March 16, 2021
5 replies
1031 views

Hi WF Community,

I'm trying to create a calculated custom column in a Timesheet report based on the following:

If totalHours is between 41-43 hours, multiple these additional hours by a factor of 1
If totalHours is greater than 43 hours, multiple these additional hours by a factor of 1.5

Examples:

If Joe Smith logged 43 hours this week, the calculated custom column would display a value of 3 (3 hours*1)
If Joe Smith logged 46 hours this week, the calculated custom column would display a value of 4.5 (3 hours*1.5)

Here's what I have for the custom column (text mode), but it's not returning any values:

displayname=Calculated Lieu Time

linkedname=direct

namekey=totalHours

querysort=totalHours

textmode=true

valueexpression=IF({totalHours}>40,({totalHours}-40)*1),IF({totalHours}>43,({totalHours}-40)*1.5,"")

valueformat=int

Does anyone know where I might be going astray?

Thanks.

Nick

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Richard_Le_

Hi Nick,

Try the below code, this should do it:

displayname=Calculated Lieu Time

linkedname=direct

namekey=totalHours

querysort=totalHours

textmode=true

valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),3),IF({totalHours}>40,SUB({totalHours},40)))

valueformat=double

What this is doing is, if the hours logged > 43, we are deducting 43 from the total hours and multiplying the remainder by 1.5, then adding 3 to that total (as we are multiplying by a factor of 1, we don't need to multiply - Ive also removed the multiply function from the second part of the expression for the same reason). That should cover your requirement.

One question I have though is how are you measuring time in lieu? Is it anything over 40 hours, or anything over 41 hours? In the expression in your original post you were looking for hours that were > 40, but you describe the calculation as being anything over 41 hours. If it anything over 41, you will want to use the following value expression instead (changes in bold):

valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),2),IF({totalHours}>41,SUB({totalHours},41)))

Does that make sense?

Best Regards,

Rich.

R

Richard_Le_

Community Advisor

Hi Nick,

Try the below code instead:

displayname=Calculated Lieu Time

linkedname=direct

namekey=totalHours

querysort=totalHours

textmode=true

valueexpression=IF({totalHours}>43,PROD(SUB({totalHours},40),1.5),IF({totalHours}>40,PROD(SUB({totalHours},40),1)))

valueformat=double

A couple of pointers for you:

Since the result of your value expression could return values that have a decimal place (because you are multiplying by 1.5), use the double valueformat. Integers will only return whole numbers.
On your existing value expression, you are checking to see if the total hours is > 40 first. This condition will always be met if the user enters a value greater than 43 and so will multiply by 1. I've reordered your expression so that we check for values >43 first, that way if a user enters 42, the first condition will fail and Workfront will then check for values >40.

Best Regards,

Rich.

N

NickVa7Author

Level 9

Thanks for this, Richard. It worked properly based on how I described the need. However, I just realized there was a flaw in how I described it. Wondering if you have any thoughts on an alternative, based on the following:

For any hours between 41-43, multiply these by 1; AND
For any hours 44+, multiple these by 1.5

Example:

Joe Smith has 47.5 Total Hours
- Lieu time calculation #1 = 41 through 43: 2hrs * 1 = 2hrs
- Lieu time calculation #2 = 44 through 47.5: 3.5hrs * 1.5 = 5.25hrs
- Total lieu time calculation = 7.25hrs

R

Richard_Le_

Accepted solution

Community Advisor

Hi Nick,

Try the below code, this should do it:

displayname=Calculated Lieu Time

linkedname=direct

namekey=totalHours

querysort=totalHours

textmode=true

valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),3),IF({totalHours}>40,SUB({totalHours},40)))

valueformat=double

What this is doing is, if the hours logged > 43, we are deducting 43 from the total hours and multiplying the remainder by 1.5, then adding 3 to that total (as we are multiplying by a factor of 1, we don't need to multiply - Ive also removed the multiply function from the second part of the expression for the same reason). That should cover your requirement.

One question I have though is how are you measuring time in lieu? Is it anything over 40 hours, or anything over 41 hours? In the expression in your original post you were looking for hours that were > 40, but you describe the calculation as being anything over 41 hours. If it anything over 41, you will want to use the following value expression instead (changes in bold):

valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),2),IF({totalHours}>41,SUB({totalHours},41)))

Does that make sense?

Best Regards,

Rich.

N

NickVa7Author

Level 9

That does make sense, thanks, Rich! Super helpful.

I'm going to have to go back to the requesting stakeholder to see if it's +40 or +41, but it's great you provided both. I shall take this learning forward.

The other part of this overall ask is here. No obligation, obviously, but if you have time and have an idea of whether this is possible, feel free to share your thoughts. Basically, the next step is trying to exclude hours from certain tasks from coming into this report.

Cheers.

N

NickVa7Author

Level 9

A possible solution to the 2nd part of my ask (noted in my previous message above), is to adapt the text mode you provided above, and make it work within an Hours report (as opposed to a Timesheet report). I tried the following modifications to the text mode, and created the column in a Hours report, but It's not returning any results.

displayname=Calculated Lieu Time

linkedname=timesheet

namekey=view.relatedcolumn

namekeyargkey.0=timesheet

namekeyargkey.1=totalHours

querysort=timesheet:totalHours

textmode=true

valueexpression=IF({timesheet:totalHours}>43,SUM(PROD(SUB({timesheet:totalHours},43),1.5),3),IF({timesheet:totalHours}>40,SUB({timesheet:totalHours},40)))

valuefield=timesheet:totalHours

valueformat=double

R

Richard_Le_

Community Advisor

Hi Nick,

Sorry to say that I don't think that this is possible in Workfront, or not that I know of anyway.

Since the Timesheet report will surface the total of all hours logged hours within a certain date range as a single report entry, the value expression I created works nicely in this scenario.

An hour report on the other hand will pull in every hour object (not just the total), so this value expression wont work in that instance because none of report entries are going to be more than 40 hours (unless your colleagues have invented a time machine to work 40+ hours in a day! 😁).

I think an hour report is probably the right way to go if you need to filter out certain hour types, but you wont be able to automate the calculation of time in lieu using this. I hate saying this (as it feels like an blasphemy!), but this might be one of those situations where you'd need to create a hour list report and export into excel to carry out the time in lieu calculations.

Sorry I couldn't help further.

Best Regards,

Rich

N

NickVa7Author

Level 9

Richard Leek, you have served your compadres admirably :). Thanks for the help.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded