Formatting time/decimal

Jakeyboy

New Member
Joined
Feb 16, 2002
Messages
6
I have a similar problem. I am a night shift worker, and my employer issues a time statement each week in the format of start time, end time, total. The problem is that in order to reproduce this in Excel, I must calculate accordingly. My result looks like this: 18:45, 08:30, 13:45. My time statement reads thus: 18:45, 08:30, 13.75. For the life of me, I can't get the calculation, (formula?) to same result. Help would be appreciated.

Thanks in advance

Jake
 
I based my formula on what you gave me but i still get a wrong answer.

anyway the payment scheme is like these
A B C D
1 6:30 8:00 1:30 40
2 6:32 7:10 0:38 18.36

my formula i did was this
=HOUR(C2)*25+IF((MINUTE(C2)>=30),(MINUTE(C2)>=30)*15+(60-MINUTE(C2))*0.42,(MINUTE(C2)<=30)*15+(MINUTE(C2)-30)*0.42)

its a bit confusing for me but this is how i understand how our payschem should be
1 hour = 25$
30 mins = 15$
execess minutes like would be charged at 0.42c

meaning if i worked for 38minutes my payment would be 15+3.36= 18.36 or if i worked 2 hours and 35minutes i would get
50+15+2.1=67.1$



but with the formula i did i get 24.24.. anybody could help me correct my formula?

thank you very much
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
On 2002-02-18 20:49, empoy wrote:
I based my formula on what you gave me but i still get a wrong answer.

anyway the payment scheme is like these
A B C D
1 6:30 8:00 1:30 40
2 6:32 7:10 0:38 18.36

my formula i did was this
=HOUR(C2)*25+IF((MINUTE(C2)>=30),(MINUTE(C2)>=30)*15+(60-MINUTE(C2))*0.42,(MINUTE(C2)<=30)*15+(MINUTE(C2)-30)*0.42)

its a bit confusing for me but this is how i understand how our payschem should be
1 hour = 25$
30 mins = 15$
execess minutes like would be charged at 0.42c

meaning if i worked for 38minutes my payment would be 15+3.36= 18.36 or if i worked 2 hours and 35minutes i would get
50+15+2.1=67.1$



but with the formula i did i get 24.24.. anybody could help me correct my formula?

thank you very much

In D2 enter: =HOUR(C2)*25+(MINUTE(C2)>=30)*15+(MINUTE(C2)>30)*(MINUTE(C2)-30)*0.42

and copy down.

Not only the specs are changed, but also your username, I think. Right?

Aladin
 
Upvote 0
Thanks very much aladin :) i eventually got it before you replied to my message but your formula is much simpler :) mine's this

=HOUR(D8)*25+IF(MINUTE(D8)<30,MINUTE(D8)*0.42,((MINUTE(D8)-30)*0.42)+(MINUTE(D8)-(MINUTE(D8)-30))*0.5)

any comments on my formula? thank you again....
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top