Forum Discussion
Struggling how to do a calculation
Hi, I am struggling on how to do a calculation, I have attached a spreadsheet below and would like to have a formula that takes the tick symbols of the contract type but only if there is no text in t...
Oh, I think you have a couple of problems. One is that you have a number of hours, whereas Excel counts hours as fractions. Add a /24 to your function to convert to hours. Secondly to display times over 24 hours without resetting then use [hh]:mm format.
I'm sorry to bother you but I don't understand it aha. are you able to help me solve this problem I need the minutes and hours adding up and being shown in an hour hour hour hour minute minute format
- The /24 is only dividing the minutes; it should be a division of the whole value. There's some excess stuff in there, so trimming that out I get:
=SUMIF(Clients!G4:G200,"*",Clients!B4:B200)/24+SUMIF(Clients!G4:G200,"*",Clients!C4:C200)/24/60
And the format should be [hh]:mm not hh:mm. I have added it to the hours lost formula =SUM(SUMIF(Clients!G4:G200,"*",Clients!B4:B200)+CONVERT(SUMIF(Clients!G4:G200,"*",Clients!C4:C200),"mn","hr")/24) and it is returning 00:15 in the hh:mm format when the hours is 40 and the minutes is 15
- No problem. The formula you have right now returns a value of 40.25. Change the formula to add a divide by 24 to the end and then apply the custom number format [hh]:mm.