If statemenet to determine hours worked per week

  • Re: If statemenet to determine hours worked per week


    Quote from NBVC;709012

    So they can enter a date in column A? Just not in the format of 4/7? If they can enter a date (no matter the format), the calculations in my formula should still work.


    I am a little confused about the "real" problem.


    They can enter a date, but it has to be entered at "7 APR 2014" and other method excel rejects. It seems that Excel does not recognize it as a date and does not allow the formuals to work to calculate the hours worked per week.

  • Re: If statemenet to determine hours worked per week


    But why can't they just enter it as you showed. Why do they have to enter it as 4/7?


    The only way to convert is to use a formula, but you can't put the formula in the data validation area.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: If statemenet to determine hours worked per week


    Will this work for all Months?


  • Re: If statemenet to determine hours worked per week


    It should. If the month has only 4 weeks, the Week 5 cell will have a 0 result.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: If statemenet to determine hours worked per week


    How would the formula change if we wanted the weeks to be calculated from Monday to Sunday of each week?


    Thank you


  • Re: If statemenet to determine hours worked per week


    Try:


    [COLOR="#0000FF"]=SUMIFS($D$4:$D$30,$A$4:$A$30,">="&$I$1-WEEKDAY($I$1,3)+7*(MOD(ROWS($A$1:$A1),7)-1),$A$4:$A$30,"<"&$I$1-WEEKDAY($I$1,3)+7*(MOD(ROWS($A$1:$A2),7)-1))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: If statemenet to determine hours worked per week


    Do you know if there is a way to make excel drop downs in a document able to scroll with the mouse wheel? If so let me know what code needs to be included.

  • Re: If statemenet to determine hours worked per week


    Click the cell with the drop down menu, then point to the up or down arrow at the right of the list and scroll with your mouse wheel.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!