Formatting time/decimal - Page 2
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Formatting time/decimal

  1. #11
    New Member
    Join Date
    Feb 2002
    Location
    Space
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,854
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #13
    New Member
    Join Date
    Feb 2002
    Location
    Space
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com