Posts by lapo1234

    Re: Date calcualtion using on workdays


    I have it now so it is calculating working days using the formula below, but I need to figure out how to account for holidays. Thanks for the help.



    =IF(AA589="Void","Void",IF(AB589="closed",NETWORKDAYS(I589,AA589),NETWORKDAYS(I589,(TODAY()))))

    I have a spreadsheet currently that I am using to calculate the number of days that something was open. I would like to modify the formula to do the calculation using only work days. The formula that I am currently using is listed below. I was thinking that the best function to use if NETWORKDAYS, and then I can have it include holidays as well. Can someone please help me with the use of this function in my formula and where to have it pull holidays from.


    Thank you in advance



    =IF(AA589="Void","Void",IF(AB589="closed",(AA589-I589),(TODAY()-I589)))

    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


    Will this work for all Months?


    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


    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


    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

    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)