# Solved Time as a decimal when over 24 hours

June 22, 2016 at 00:30:48
Specs: Windows 7
 for under 24 hours I use (A2-INT(A2)*24) this works with anything from 00:00:00 to 23:59:59 after that I get serious issues, any ideas on what I can use instead, I am looking at calculating UPH for jobs done so the end formula would be something like:B2/((A2-INT(A2))*24)where B2 is my total units completed and A2 total time takenmessage edited by Rik-Sprint

See More: Time as a decimal when over 24 hours

June 23, 2016 at 11:34:46
 Pretty sure this will work, but have not tested it extensivelyChange your formula from: =B2/((A2-INT(A2))*24)to simply: =B2/(B2*24) =B2/(A2*24)See how it work for you.Correction, see above.MIKEmessage edited by mmcconaghy

#1
June 22, 2016 at 03:15:55
 I'm not sure that I understand your problem. Dates and times are stored by Excel as integers, so you can do all the normal arithmetic operations on them. It's only when they are formatted in a cell that they are displayed differently.

Report •

#2
June 22, 2016 at 03:26:38
 I have a spreadsheet where over the course of a week I input data in one column as timethe second column as total qtyexample4:00 18003:40 1600the total time taken was then shown as7:40 3400I then use the formula 3400 / 7:40 to show how many per hour were donethe formula B2/((A2-INT(A2))*24) will give me my hourly rate, however if the total time taken is greater than 24 hours then the formula does not work as it creates an hourly rate larger than the total number of units donenow I can counteract this with an if formula so if it greater than 24 hours it add 24 to the decimal given in the above formula, but this is assuming the total time is 24 hours and not 25 hours or moreActual Formula in the sheet=IFERROR(IF(I20>\$A\$2,F20/((I20-INT(I20))*24+24),F20/((I20-INT(I20))*24)),0)message edited by Rik-Sprint

Report •

#3
June 22, 2016 at 03:42:20
 ijack,re: "Dates and times are stored by Excel as integers" To be more precise, Dates are stored as integers and Time is store as the decimal portion of a given day.e.g. ddddd.ttttt

Report •

Related Solutions

#4
June 22, 2016 at 11:38:56
 Without seeing how your sheet is setup, the best I can offer is to Custom Format your Time cells as: ` [HH]:MM `The brackets are required to tell Excel that the time may be over 24 Hrs.MIKEhttp://www.skeptic.com/

Report •

#5
June 22, 2016 at 23:38:19
 @mmcconaghythe cell is formatted for [HH]:MM formating is not the problemlet me give two examples[HH]:MM is the formating of my cell reference that is used in the formula[HH:MM]23:59 =(B2-INT(B2))*24 =23.9833 (20000 units / Time taken (23.9833) = 834 per hourthereforless than 24 hours work time[HH]:MM23:59 =([HH]:MM-INT([HH]:MM))*24 =23.9833 (20000 units / Time taken (23.9833) = 834 per hourMore than 24 hours work time[HH:MM]24:01 =([HH]:MM-INT([HH]:MM))*24 =0.0167 (20000 units / Time taken (0.0167) = 1197604 per hour this is more per hour than the total done in the 24 hour and 1 minute periodI can do a if to add in the 24 hours if the time is greater than 23:59 but that also assumes all time over 23:59 is less than 24:59 I will have to manually adjust for 25:00, 26:00, 27:00 I would prefer to have this automated in a formulaI hope this helps explain the issue

Report •

#6
June 22, 2016 at 23:45:59
 ```Decimal Time 0.0000 00:00 0.0167 00:01 0.0333 00:02 23.9667 23:58 23.9833 23:59 0.0000 24:00 0.0167 24:01 1.0000 25:00 1.0167 25:01 ```formula for column A=(B2-INT(B2))*24here are the results of using my formula, as you can see, the integer returned at 24 hour range is also the same as those returned at 00 hour range, at 25 hours it returns the ranges at which it should be returning 24 hours

Report •

#7
June 23, 2016 at 05:18:55
 I'm still not completely clear on what your doing.Your formula converts hours to their decimal equivalent.So for 23:59 you get 23.98 where the 98 is the percentage of the hour.Convert 23:59 from H:MM to Number format and you get: 0.999305which is the percentage of a Day (24 hours) So 25 hours would be 1 day and .04 percent of the next day.Things can sometime get tricky when dealing with Time. MIKEhttp://www.skeptic.com/

Report •

#8
June 23, 2016 at 05:59:49
 ok so here is the table I have, this is compiled by pulling in data filled in on another TAB on daily basis```Week Date Commencing Week Ending Containers CTNS SKUs Staff Total Tip Time CTNs Per Hour CTNS Per Person Per Hour Per Container WEEK 1 04/01/2016 10/01/2016 0 0 0 0 0:00:00 0 0 WEEK 2 11/01/2016 17/01/2016 0 0 0 0 0:00:00 0 0 WEEK 3 18/01/2016 24/01/2016 0 0 0 0 0:00:00 0 0 WEEK 4 25/01/2016 31/01/2016 0 0 0 0.0 0:00:00 0 0 WEEK 5 01/02/2016 07/02/2016 0 0 0 0.0 0:00:00 0 0 WEEK 6 08/02/2016 14/02/2016 0 0 0 0.0 0:00:00 0 0 WEEK 7 15/02/2016 21/02/2016 0 0 0 0.0 0:00:00 0 0 WEEK 8 22/02/2016 28/02/2016 0 0 0 0.0 0:00:00 0 0 WEEK 9 29/02/2016 06/03/2016 0 0 0 0.0 0:00:00 0 0 WEEK 10 07/03/2016 13/03/2016 0 0 0 0.0 0:00:00 0 0 WEEK 11 14/03/2016 20/03/2016 0 0 0 0.0 0:00:00 0 0 WEEK 12 21/03/2016 27/03/2016 0 0 0 0.0 0:00:00 0 0 WEEK 13 28/03/2016 03/04/2016 6 8368 267 4.0 14:40:00 571 143 WEEK 14 04/04/2016 10/04/2016 1 1822 116 4.0 3:45:00 486 121 WEEK 15 11/04/2016 17/04/2016 2 2293 158 4.0 5:15:00 437 109 WEEK 16 18/04/2016 24/04/2016 4 5621 315 4.0 11:20:00 496 124 WEEK 17 25/04/2016 01/05/2016 7 11884 646 4.0 21:55:00 542 136 WEEK 18 02/05/2016 08/05/2016 7 11123 454 4.0 19:40:00 566 141 WEEK 19 09/05/2016 15/05/2016 6 9326 367 3.9 18:55:00 493 126 WEEK 20 16/05/2016 22/05/2016 1 1920 132 4.0 5:20:00 360 90 WEEK 21 23/05/2016 29/05/2016 8 12098 442 4.0 24:05:00 145176 36294 WEEK 22 30/05/2016 05/06/2016 6 10115 395 4.0 21:30:00 470 118 WEEK 23 06/06/2016 12/06/2016 2 2528 90 4.0 5:10:00 489 122 WEEK 24 13/06/2016 19/06/2016 3 3948 219 3.3 9:50:00 401 120 WEEK 25 20/06/2016 26/06/2016 4 5950 272 3.8 13:40:00 435 116 WEEK 26 27/06/2016 03/07/2016 3 5747 314 0.0 0:00:00 0 0 WEEK 27 04/07/2016 10/07/2016 9 15794 698 0.0 0:00:00 0 0 WEEK 28 11/07/2016 17/07/2016 0 0 0 0.0 0:00:00 0 0 WEEK 29 18/07/2016 24/07/2016 0 0 0 0.0 0:00:00 0 0 WEEK 30 25/07/2016 31/07/2016 0 0 0 0.0 0:00:00 0 0 WEEK 31 01/08/2016 07/08/2016 0 0 0 0.0 0:00:00 0 0 WEEK 32 08/08/2016 14/08/2016 0 0 0 0.0 0:00:00 0 0 WEEK 33 15/08/2016 21/08/2016 0 0 0 0.0 0:00:00 0 0 WEEK 34 22/08/2016 28/08/2016 0 0 0 0.0 0:00:00 0 0 WEEK 35 29/08/2016 04/09/2016 0 0 0 0.0 0:00:00 0 0 WEEK 36 05/09/2016 11/09/2016 0 0 0 0.0 0:00:00 0 0 WEEK 37 12/09/2016 18/09/2016 0 0 0 0.0 0:00:00 0 0 WEEK 38 19/09/2016 25/09/2016 0 0 0 0.0 0:00:00 0 0 WEEK 39 26/09/2016 02/10/2016 0 0 0 0.0 0:00:00 0 0 WEEK 40 03/10/2016 09/10/2016 0 0 0 0.0 0:00:00 0 0 WEEK 41 10/10/2016 16/10/2016 0 0 0 0.0 0:00:00 0 0 WEEK 42 17/10/2016 23/10/2016 0 0 0 0.0 0:00:00 0 0 WEEK 43 24/10/2016 30/10/2016 0 0 0 0.0 0:00:00 0 0 WEEK 44 31/10/2016 06/11/2016 0 0 0 0.0 0:00:00 0 0 WEEK 45 07/11/2016 13/11/2016 0 0 0 0.0 0:00:00 0 0 WEEK 46 14/11/2016 20/11/2016 0 0 0 0.0 0:00:00 0 0 WEEK 47 21/11/2016 27/11/2016 0 0 0 0.0 0:00:00 0 0 WEEK 48 28/11/2016 04/12/2016 0 0 0 0.0 0:00:00 0 0 WEEK 49 05/12/2016 11/12/2016 0 0 0 0.0 0:00:00 0 0 WEEK 50 12/12/2016 18/12/2016 0 0 0 0.0 0:00:00 0 0 WEEK 51 19/12/2016 25/12/2016 0 0 0 0.0 0:00:00 0 0 WEEK 52 26/12/2016 01/01/2017 0 0 0 0.0 0:00:00 0 0 ```week 21 the total time goes over 23:59 creating a massive issue with my datathe data hasnt really posted in as i expected, I hope you can see the issue thoughmessage edited by Rik-Sprint

Report •

#9
June 23, 2016 at 09:13:03
 I'm at work right now, so please be patient.I'll get back to you tonight, unless someone else jumps in with an answer.MIKEhttp://www.skeptic.com/

Report •

#10
June 23, 2016 at 11:34:46