If statemenet to determine hours worked per week

    I have a spreadsheet with the columns labels date (A), work done, hours (D).


    I want to have excel calculate the number of hours spent during a week to be able to sum up. The number of entries made per week can vary so I was going to have excel calculate in another column either the hours listed in D or enter a 0 which I can then sum to get the hours worked per week. I tried using the following formula but am getting an error. Can someone help me with what I am missing.


    Thanks


    =IF((OR(A5=DATEVALUE("04/06/2014"), A5=DATEVALUE("04/07/2014"), A5=DATEVALUE("04/08/2014"), A5=DATEVALUE("04/09/2014"), A5=DATEVALUE("04/10/2014"), A5=DATEVALUE("04/11/2014"), A5=DATEVALUE("04/12/2014")),D5,0)

    Re: If statemenet to determine hours worked per week


    Can you post a sample workbook showing your setup?

    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


    OK. Just thinking there is probably a better way or two....

    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


    What is the ultimate goal? Is it to sum up the weekly hours?

    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


    Quote from NBVC;708689

    What is the ultimate goal? Is it to sum up the weekly hours?



    Sum up weekly hours, based on dates entered. So number of hours worked in the 1st week of April (April 1-5) 2nd week (6-12), ....

    Re: If statemenet to determine hours worked per week


    Change the date in I1 to an actual date, April 1, 2014 (you can format the cell to MMM YYYY if you don't want the day to show)...


    Then try formula in K7
    [COLOR="#0000FF"]
    =SUMIFS($D$4:$D$30,$A$4:$A$30,">="&$I$1-WEEKDAY($I$1,3)-1+7*(MOD(ROWS($A$1:$A1),7)-1),$A$4:$A$30,"<"&$I$1-WEEKDAY($I$1,3)-1+7*(MOD(ROWS($A$1:$A2),7)-1))
    [/COLOR]
    copied down. This counts from Sunday to Saturday of each week of the month.


    Adjust the ranges to suit your A and D column ranges.

    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


    I am rolling this out to an office, and both my original method and the method suggested by NBVC work on most computers. However, there are a couple computers where when entering the date in column A it is not letting a date be entered as 4/7 the only way that it will accept a date to be entered is if you type 7 APR 2014. The field is restricted through data validation to make sure that it is a April 2014 date. Also the weekly summing is not working on these computers. I am guessing that the same thing is preventing both from working. Does anyone have any suggestions about what I need to change on the couple computers to make sure that they will be able to have the weekly hours totaled? If not does anyone have any ideas about how I can get it to calculate the weekly hours?


    Thanks in advance

    Re: If statemenet to determine hours worked per week


    Ensure the formatting of those cells is not TEXT.

    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


    The formatting is DATE. I opened the same file they are using on my computer and it works fine. This has been deployed to about 80 people and as of now there are only 2 known instances.

    Re: If statemenet to determine hours worked per week


    Can't think of anything else... maybe a regional settings thing? Check and compare. Also check if they have same versions and SP levels.

    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


    Thanks for the help. What is the easiest was to check the regional settings and the SP levels? Would doing a reinstall potentially help?

    Re: If statemenet to determine hours worked per week


    For version, it depends on Office version you are using: http://office.microsoft.com/en-ca/excel-help/what-version-of-office-am-i-using-HA101873769.aspx


    Regional Settings via the Control Panel...


    Maybe a re-install would help. Can't know for sure though.

    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


    It was just determined that the only 2 computers that are having this issue have their date setting in the toolbar set to display the date as 07 APR 2104, as opposed to the traditional 4/7/2014. Any idea on how to prevent excel from being upset over this change?

    Re: If statemenet to determine hours worked per week


    That should be in the Regional Settings. Go to your Control Panel, then Region and Language. Change the Short Date format.

    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


    The two people who have the date format this way have it set that way for a other programs from documentation. Is there any way to prevent Excel from seeing that date format?

    Re: If statemenet to determine hours worked per week


    No, I don't think so. Excel gets it's date information from the computer settings.

    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


    Would it be helpful to have do some sort of calculation to change the format.

    Re: If statemenet to determine hours worked per week


    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.

    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!

Register Yourself Login