2

I have the basic time value formula, but I am having two problems;

The first is with this Total Comp Time formula:

=TEXT([Start Time]-[End Time],"h:mm")

It does display the difference in time as hours and minutes 1:30 as shown in the image. I would prefer it to show 1.5 because when the time is being manually deducted as 1, .5, or .25 of an hour (Comp Time Taken ex.in the image .5) the calculated column Comp Time Left is not accurate.

The formula being used for Comp Time Left:

=TEXT([Total Comp Time]-[Comp Time Taken],"h:mm")

Part 2 of my problem is I only wanted Total Comp Time to appear only if the "Status" column says "Approved"

I started on this formula below, but I can't seem to get it to work:

=IF([Status]=Approved, TEXT([Start Time]-[End Time],"h:mm"), “0”)

Comp time columns

0

2 Answers 2

0

=IF([Status]="Approved",LEFT((TEXT([End Time]-[Start Time],"hh:mm")),2)+(RIGHT((TEXT([End Time]-[Start Time],"hh:mm")),2)/60),"") (return as number with 2 decimal points)

2
  • it still didn't properly calculate the time to display 1.5 ,.5, or .25. Should this part LEFT((TEXT([End Time]-[Start Time],"hh:mm")),2) have a 1 at the end instead of 2?
    – Mel
    Apr 12, 2016 at 19:53
  • It sounds like you have "h:mm" instead of "hh:mm"
    – Erin L
    Apr 13, 2016 at 13:35
0

To get the time as a fraction of an hour then you'd do ([Start Time]-[End Time]) * 24.

Start Time | End Time | In H:MM | In Fraction
------------------------------------------------
     14:00 |    14:05 |    0:05 |  0.083333333
     14:00 |    14:10 |    0:10 |  0.166666667
     14:00 |    14:15 |    0:15 |  0.25
     14:00 |    14:20 |    0:20 |  0.333333333
     14:00 |    14:25 |    0:25 |  0.416666667
     14:00 |    14:30 |    0:30 |  0.5
     14:00 |    14:45 |    0:45 |  0.75
     14:00 |    14:55 |    0:55 |  0.916666667
     14:00 |    15:00 |    1:00 |  1
     14:00 |    15:05 |    1:05 |  1.083333333
     14:00 |    15:30 |    1:30 |  1.5
2
  • So enter =TEXT([Start Time]-[End Time]*24)
    – Mel
    Apr 12, 2016 at 20:00
  • I don't think you need the TEXT formula. You should just be able to use ([Start Time]-[End Time]) * 24 and set the field to a number type with how many ever decimal places you want. Apr 12, 2016 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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