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 taken

message edited by Rik-Sprint


See More: Time as a decimal when over 24 hours

Report •


✔ Best Answer
June 23, 2016 at 11:34:46
Pretty sure this will work, but have not tested it extensively

Change 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.


MIKE

http://www.skeptic.com/

message 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 time

the second column as total qty

example

4:00 1800
3:40 1600

the total time taken was then shown as

7:40 3400

I then use the formula 3400 / 7:40 to show how many per hour were done

the 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 done

now 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 more


Actual 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

MIKE

http://www.skeptic.com/


Report •

#5
June 22, 2016 at 23:38:19
@mmcconaghy

the cell is formatted for [HH]:MM formating is not the problem

let 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 hour

therefor

less than 24 hours work time


[HH]:MM
23:59 =([HH]:MM-INT([HH]:MM))*24 =23.9833 (20000 units / Time taken (23.9833) = 834 per hour

More 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 period

I 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 formula


I 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))*24

here 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.999305
which 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.

MIKE

http://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 data


the data hasnt really posted in as i expected, I hope you can see the issue though

message 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.

MIKE

http://www.skeptic.com/


Report •

#10
June 23, 2016 at 11:34:46
✔ Best Answer
Pretty sure this will work, but have not tested it extensively

Change 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.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
June 23, 2016 at 22:18:13
that actually worked, I have no idea why I over complicated the damn formula using an integer

thank you


Report •


Ask Question