I have data that has a start date, end date and total hours
I need to get it split out by individual day
The hours would be divided by the number of days and each day would get that amount of hours
Employee StartDate EndDate Hours
Test1 1/31/11 2/4/11 55
Test 2 2/9/11 2/11/11 16
Test1 2/28/11 3/4/11 40
I'm trying to do this because ultimately I need to get the total hours for a month, but ranges can span different months.
Any suggestions appreciated.
Thanks
Do you mean =hours/(enddate-startdate+1) ?
The idea is a good one but will all Tests span similar date ranges ?
In terms of calculating monthly hours etc without the daily values - if we assume source values are A1:D4 (row 1 headers):
above would return:
Please Login or Register to view this content.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi, DonkeyOte
"Test1", "Test2" would actually be employee last names (which I would probably group on in maybe a pivot table).
Ranges will be weekdays and could be anywhere from 1 day (start/end are same) to 5 days. Currently, no weekends, but I guess that is a possibility.
I will test this as soon as I get to work (I woke up with this bugging me)
I think I'm lost - I've attached a spreadsheet. I think my data doesn't look like what you wrote the formulas for.
The formulae I posted should work ok you just need to revise the ranges used to meet your own requirements, eg:
Note the array entry for G2Please Login or Register to view this content.
The above would generate:
With arrays (& SUMPRODUCT) the precedent ranges should be kept as lean as possible.Please Login or Register to view this content.
100 rows or as as used here won't hurt but if using with much larger data sets it would be worth considering use of Dynamic Named Ranges (using non-volatile construct - eg INDEX & MATCH).
Hi, DonkeyOte,
I appreciate your help, but it is still not working - see attached sheet
It would I think help if we stuck to a consistent sample layout and applied the formulae to the cells suggested.
Attached is the sample from your penultimate post with the formulae copied & pasted from my subsequent post (arrays confirmed obviously)
Thank you. I started in the wrong column - sorry, I get that now.
Thank you very much for your assistance!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks