Solved time accrual in Excel

May 14, 2013 at 13:19:30
Specs: Windows 7
we accrue 4.20 hours each pay period. After 3 pay periods the accrual total is 12.60 which is actually 13 hours. How do I convert 12.60 to 13?

another example: I have a beginning accrued total of 17.20 and I use 8.30 hours. This leaves me a balance of 8.90 hours which is actually 9.30 hours. How do I convert this?


See More: time accrual in Excel

Report •


✔ Best Answer
May 14, 2013 at 19:21:49
vhubbard,

Let me try to explain why your question is confusing. In the first part of your original post, you appear to be using a decimal point for time instead of the colon that many of us are used to.

From this statement, "After 3 pay periods the accrual total is 12.60 which is actually 13 hours" I surmise that 4.20 means 4 hours 20 minutes.

Let's do the math your way:

3 * 4.20 = 12.60 or 12 hours 60 minutes or 13 hours

Most of us (including Excel) would use 4:20 for 4 hours 20 minutes. In Excel,
3 * 4:20 would equal 13.00, just like you are looking for.

Now, I guess it's OK to denote time with a decimal point method, but the problem is that you are not being consistent. This is where your method fails:

"I have a beginning accrued total of 17.20 and I use 8.30 hours. This leaves me a balance of 8.90 hours which is actually 9.30 hours"

Using your method, that would look like this:

17.20 - 8.30 = 8.90

or

17 hours 20 minutes minus 8 hours 30 minutes = 8 hours 90 minutes.

The problem is that 17 hours 20 minutes minus 8 hours 30 minutes is not 8 hours 90 minutes, it's actually 8 hours 50 minutes.

17:20 - 8:30 = 8:50

Since there are only 60 minutes in an hour, you can't simply subtract your decimal portion and expect the minutes to come out correctly.

If you want to use Excel to convert times for you, you either have use the standard time formats (4:20) or realize that 4.2 hours is actually 4:12 since
.20 x 60 = 12.

I hope that helps.

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



#1
May 14, 2013 at 13:30:44
Format the cells containing times as Time and let Excel do the conversion. Once you have told Excel that the numbers represent times it does the conversion for you as it knows know that there are sixty minutes in an hour. Excel has all the functions for date/time calculations built in.

Stuart


Report •

#2
May 14, 2013 at 13:44:10
Thank you for this suggestion. However, I guess it is a combination of 2 things, total hours accrual and then converting to 60 minutes but not AM/PM time.

Report •

#3
May 14, 2013 at 15:56:32
Format the cells to display a 12 hours or 24 hour clock as required. You have that option.

Excel actually process dates and times as a floating point number. If you are getting a result of 8:90 you are not formatting the value as a time.

17.20 - 8.30 is 8:50. No idea where you get 8:90 or 9:30 from.

Stuart


Report •

Related Solutions

#4
May 14, 2013 at 19:21:49
✔ Best Answer
vhubbard,

Let me try to explain why your question is confusing. In the first part of your original post, you appear to be using a decimal point for time instead of the colon that many of us are used to.

From this statement, "After 3 pay periods the accrual total is 12.60 which is actually 13 hours" I surmise that 4.20 means 4 hours 20 minutes.

Let's do the math your way:

3 * 4.20 = 12.60 or 12 hours 60 minutes or 13 hours

Most of us (including Excel) would use 4:20 for 4 hours 20 minutes. In Excel,
3 * 4:20 would equal 13.00, just like you are looking for.

Now, I guess it's OK to denote time with a decimal point method, but the problem is that you are not being consistent. This is where your method fails:

"I have a beginning accrued total of 17.20 and I use 8.30 hours. This leaves me a balance of 8.90 hours which is actually 9.30 hours"

Using your method, that would look like this:

17.20 - 8.30 = 8.90

or

17 hours 20 minutes minus 8 hours 30 minutes = 8 hours 90 minutes.

The problem is that 17 hours 20 minutes minus 8 hours 30 minutes is not 8 hours 90 minutes, it's actually 8 hours 50 minutes.

17:20 - 8:30 = 8:50

Since there are only 60 minutes in an hour, you can't simply subtract your decimal portion and expect the minutes to come out correctly.

If you want to use Excel to convert times for you, you either have use the standard time formats (4:20) or realize that 4.2 hours is actually 4:12 since
.20 x 60 = 12.

I hope that helps.

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


Report •

#5
May 14, 2013 at 19:28:06
StuartS:

re: "17.20 - 8.30 is 8:50"

No, 17.20 - 8.30 is 8.90. I know of no way to get Excel to return 8:50 (a time) from a decimal based subtraction operation.

I understand your point, but you are mixing time notation with decimal notation, just like the OP is. I only bring this up to help eliminate the confusion that the OP has already caused.

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


Report •

#6
May 15, 2013 at 01:56:54
DerbyDad03:

I didn't notice the decimal notation. I was assuming the OP was using time notation throughout. Your explanation makes it clearer - use time notation when doing time calculations.

Stuart


Report •


Ask Question