0

Hello I have three fields hours1, hours2 and total hours.

Total hours is calculated fields based on the sum of hours1 and hours2.

I use the formula =VALUE(TEXT(total hours,"hh").

It works if sum of the 2 fields are whole numbers. for example. for (18:00 - 16:00) result is 2. But if there are minutes in time i.e. (18:30 -16:00) result will be 2.12, and I am looking for the 2.5 result.

Can anyone help?

Thank you

7
  • what formula you have used for Total hours field please post that. Commented Nov 20, 2018 at 9:34
  • hi Ganesh...i use a simple addition <!-- =TotHrs1+TotHrs2 -->
    – Newbie
    Commented Nov 20, 2018 at 11:15
  • Are they (hours1 and hours2, or "ToHrs1 and TolHrs2") date and time column? You need to provide detail information about your fields. Commented Nov 21, 2018 at 1:51
  • Apologies for not making any sense on the initial question...and I will rephrase it. I am trying to make a sharepoint list for Overtime. Basically the overtime form has sections for first 12 hours (00:00 - 12:00) and secondly for (12:00 - 00:00). The fields here are FromHrs1, ToHrs1, FromHrs2, ToHrs2. There are two calculated field TotHrs1 and TotHrs2 that calculates the difference of the above fields to get the number of overtime hours with the following formula =ToHrs1-FromHrs1 and ToHrs2-FromHrs2
    – Newbie
    Commented Nov 21, 2018 at 7:16
  • And lastly another calculated field called HrsEarned is used to sum TotHrs1 and TotHrs2 using the formula =TotHrs1+TotHrs2 The above works properly on the list...but there is a requirement for me to show total of overtime hours of staffs on a monthly basis, and I can only do it when creating a new view as Datasheet View. Since I am unable to get the field HrsEarned in TOTALS...i thought of creating another calculated field called CumputedHours and put the formula =VALUE(TEXT(HrsEarned,"hh").
    – Newbie
    Commented Nov 21, 2018 at 7:17

1 Answer 1

0

Use the following formula:

=24*(time1-time2)

Return Number type with 1 or 2 decimal. Don't select Automatic decimal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.