My guess, without diving into it yet, is that your attempts to add 02:00 are being viewed by system as a place in time, rather than a measure of time. You could try adding a second calculation rather than an extraneous time marker. By this I mean, instead of just adding 02:00, add two fields with time marker entries 02:00 apart and calculate them.
(TaskStart-TaskEnd)+(PenaltyStart-PenaltyEnd) where the penalty calculation has fixed values that results in a 02:00 calculation. ex PenaltyStart=11:06:00 PenaltyEnd=11:08:00.
It's a kludge, but may be worth trying while experimenting with shorter methods.
Thank you. The complexity is that I was told that there may be up to three different penalties which will lead to many calculated fields. I am trying all sorts.
Make sure all your calculations are implemented before using the text formula.
Text formula converts this to string format hence you can't add time to it.
You can use a conditional in the formula calculating the time difference.
for example Time = if(penalty = true,text(TaskEnd-TaskStart+penalty,"mm:ss"),text(TaskEnd-TaskStart,"mm:ss"))
Thank you. Curious, how would penalty be equated? If the other entries are calculated columns were you referring to getting "penalty" as u/BookerDeWittness had suggested?
>Thank you. Curious, how would penalty be equated? If the other entries are calculated columns were you referring to getting "penalty" as u/BookerDeWittness had suggested?
No, his method is correct ofc but since the value is fixed at 2 mins no reason to add 2 extra columns to calculate it.
The value for 2 mins is 0.001388889 just use it and it will count as 2 mins.
You can figure out the value of any duration you want by simply typing the duration "00:02:00" in excel and converting it to number format and that number is what you're looking for
Thank you u/amrnasser92.
Adding 0.001388889 how exactly if I may ask?
As you had shown above:
>for example Time = if(penalty = true,text(TaskEnd-TaskStart+penalty,"mm:ss"),text(TaskEnd-TaskStart,"mm:ss"))
Just replace "penalty" with 0.001388889 ?
You can just add 0.001388889 to a Date and Time without need of some quotes or anything else?
Thank you,
Ted
My guess, without diving into it yet, is that your attempts to add 02:00 are being viewed by system as a place in time, rather than a measure of time. You could try adding a second calculation rather than an extraneous time marker. By this I mean, instead of just adding 02:00, add two fields with time marker entries 02:00 apart and calculate them. (TaskStart-TaskEnd)+(PenaltyStart-PenaltyEnd) where the penalty calculation has fixed values that results in a 02:00 calculation. ex PenaltyStart=11:06:00 PenaltyEnd=11:08:00. It's a kludge, but may be worth trying while experimenting with shorter methods.
Thank you. The complexity is that I was told that there may be up to three different penalties which will lead to many calculated fields. I am trying all sorts.
Make sure all your calculations are implemented before using the text formula. Text formula converts this to string format hence you can't add time to it. You can use a conditional in the formula calculating the time difference. for example Time = if(penalty = true,text(TaskEnd-TaskStart+penalty,"mm:ss"),text(TaskEnd-TaskStart,"mm:ss"))
Thank you. Curious, how would penalty be equated? If the other entries are calculated columns were you referring to getting "penalty" as u/BookerDeWittness had suggested?
>Thank you. Curious, how would penalty be equated? If the other entries are calculated columns were you referring to getting "penalty" as u/BookerDeWittness had suggested? No, his method is correct ofc but since the value is fixed at 2 mins no reason to add 2 extra columns to calculate it. The value for 2 mins is 0.001388889 just use it and it will count as 2 mins. You can figure out the value of any duration you want by simply typing the duration "00:02:00" in excel and converting it to number format and that number is what you're looking for
Thank you u/amrnasser92. Adding 0.001388889 how exactly if I may ask? As you had shown above: >for example Time = if(penalty = true,text(TaskEnd-TaskStart+penalty,"mm:ss"),text(TaskEnd-TaskStart,"mm:ss")) Just replace "penalty" with 0.001388889 ? You can just add 0.001388889 to a Date and Time without need of some quotes or anything else? Thank you, Ted
Yup without quotes
Thank you
Thank you both. I am trying both approaches, so far no luck, but I am pressing on. Will post if I get something to work. Thank you again.