+ Reply to Thread
Results 1 to 8 of 8

If formula with calculations

  1. #1
    Registered User
    Join Date
    05-23-2020
    Location
    Tanzania
    MS-Off Ver
    Office 365 Home Edition
    Posts
    6

    If formula with calculations

    Hi Everyone,

    I hope you all are well.

    Am trying hard with an IF formulas trying to calculate hours of overtime for driver project.

    This is the formula
    1.jpg
    (I tried to attach the file, but something it's wrong with the attachment option)

    So the formula is: =IF(C4="Monday",(J1-D4)+(E4-K1),"") and it is calculating correct.


    But when I want to add other conditions inside the formula the problems show up.
    =IF(C4="Monday",(J1-D4)+(E4-K1),""),IF(C4="Saturday",(J2-D4)+(E4-K2),"")) : You've entered too many arguments for this functions.

    Why does this happen? And how can I overcome this problem?

    I am trying to calculate the regular hrs with to hrs based on the weekdays and holidays.

    Kindly, I will appreciate a lot your help.
    Regards,

  2. #2
    Registered User
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    2007, 2010, 2019
    Posts
    14

    Re: If formula with calculations

    This formula is basically incorrect.
    This the first complete IF
    IF(C4="Monday",(J1-D4)+(E4-K1),"")
    After a comma, you have another
    IF(C4="Saturday",(J2-D4)+(E4-K2),"")
    And you finish everything with a right bracket ) without its left counterpart ( what is wrong itself, no matter what is the rest of the formula.

    What do you really want to calculate? The time for Monday plus time for Saturday or something else?
    Last edited by PKowalik; 05-23-2020 at 08:02 PM.
    Przemyslaw Kowalik, Lublin Univ. of Technology

  3. #3
    Registered User
    Join Date
    05-23-2020
    Location
    Tanzania
    MS-Off Ver
    Office 365 Home Edition
    Posts
    6

    Re: If formula with calculations

    Sorry, PKowalik you are correct. I forgot to remove the bracket.

    =IF(C4="Monday",J1-D4+E4-K1,"", IF(C4="Saturday",J2-D4+E4-K2,"")) (Result : You've entered too many arguments for this functions.)

    I want to calculate the overtime based on weekdays, Saturday, Sundays, and Holidays.

    For Example;

    On Weekdays (Monday up to Friday) the Morning Regular time start from 07:30 AM and in the Evening the Regular time end on 17:00 PM
    So let say that the driver starts the work on 06:00 AM and his end reporting time is 18:00 PM
    Now we have 1 hr of OT in the morning and 1 hr of OT in the evening. (1+1= 2 hr of OT)

    IF A="Monday" then (morning reporting time - fixed time) + (fixed time - evening time)
    This must give us a total of overtime done by the Driver.
    If I use only this formula is correct. But when I want to add more conditions like IF A="Saturday" (because Saturday has different fixed Time, actually it's a half working day) the message " You've entered too many arguments for this functions" shows up.

    Hope I was clear :/

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,832

    Re: If formula with calculations

    With your first formula, you want to test C4 for Monday and leave the cell blank if C4 is not Monday. You are doing that with the ,"") . Then you want to modify the formula to check for C4=Saturday, if it's not Monday. The Saturday test must replace the "" argument in the original formula.
    =IF(C4="Monday",(J1-D4)+(E4-K1),IF(C4="Saturday",(J2-D4)+(E4-K2),""))
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    05-23-2020
    Location
    Tanzania
    MS-Off Ver
    Office 365 Home Edition
    Posts
    6

    Re: If formula with calculations

    Thank you, protonLeah.

    I appreciate a lot you help.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-23-2020
    Location
    Tanzania
    MS-Off Ver
    Office 365 Home Edition
    Posts
    6

    Re: If formula with calculations

    This thread is Solved.
    Thanks to protonLeah

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,687

    Re: If formula with calculations

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Registered User
    Join Date
    05-23-2020
    Location
    Tanzania
    MS-Off Ver
    Office 365 Home Edition
    Posts
    6

    Re: If formula with calculations

    Thanks, dear.
    Done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1