Thank you BobBridges for the response. It calculates the total hours for the date range that I put in the formula and is pulling the hours from the imported information from the timesheet. What I was hoping to do was maybe to figure out if I could add a part into the formula where it says that if the day is today or later to calculate the working hours between the date range in the formula and put that total in the field.
So say I have a date range of 9/1/2013 through 9/6/2013 this is what I typed into the formula listed below is there a way to have it calculate working hours if that date range is after todays date which today is 8/8/2013, also since there is a holiday in there I would like it to take that into account. I know I am probably asking alot. I am trying to keep the form as clean as possible. Maybe I should just make a housekeeping sheet and hide it from view. what are your thoughts.
Thanks again
Quote:
Originally Posted by BobBridges
If I follow this, you're saying that your worksheet calculates hours correctly up to this date, up to TODAY() I mean, but now you want it to calculate how many hours are left. I see two possibilities:
1) If your contract has (say) 2000 hours on it, and your worksheet shows in F23 that so far 823 hours have been expended, then the hours remaining until the end of the contract are simply 2000 - F23 = 1177.
2) If you want to calculate the working hours remaining between today and 2013-09-20, then the number of hours worked so far doesn't come into it at all; it's just a matter of figuring out how many working days there are between today and 09-20 and multiplying by 8-or-whatever. As I recall, Excel has some functions that handle working hours and days in an add-on. Let's see....here it is; NETWORKDAYS "returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays." But this doesn't say anything about having to install an add-on; maybe they've made it standard in the latest versions of Excel. (I only recently migrated from 2003 to 2010.)
|