/ Excel wizards of the world unite - date calculation problem
I'm hoping that some of you clever chaps can help me out.
I've got a data set with lots of dates and times in, and need to calculate the working days between the two. I've googled for most of the afternoon and can only come up with workdays between two dates which gives me whole numbers.
The formula in which it needs to be is:
Where P65=2 defines whether it is the first and second dates in that particular transaction, and D65 is the time and date of the second transaction, and D64 the time and date of the first.
I need the data presented as a fraction of the work days taken to process.
Is this possible, or am I just dreaming the impossible dream?
If it helps, I have the days of the week in a separate field, could they be used to modify the data appropriately?
cheers all, GTE
if you want working days only then you will need to use the networkdays function. I would then pull out the time into a separate calc and figure the decimal part from that).
If you want to count holidays etc then thats when it gets tricky. although that function has it in there you need to enter the exclusions.
You've lost me slightly as to what it is you want but if you have your date in Cell A4 and set Cell A5 to =A4 and then Format Cell A5 to be a number this gives you a date and time as a decimal fraction.
This can then be manipulated to your heart's content
Does this do it?
=IF(P65 = 2,SUM(NETWORKDAYS(D64,D65)-2,1-(D64-INT(D64)),D65-INT(D65)),"")
Assumes that your start and end dates are always workdays.
Too be honest, not completely understood the objective
Networkdays would be a start point, but it works in whole days so you would want to add on the difference between the start time and the end of the first business day, then subtract the difference between the end time and the start of the last business day.
I'm on an iPhone at the moment, otherwise I'd look at an example - I like puzzles like this!
Thanks Andy, this seems to work just how I wanted it.
Not quite sure how it does what it does, but I'm happy with that!
End dates will always be workdays, start dates could be weekends, but it's made my data a lot more accurate than it could have been! I can make the other adjustments manually. This has massively reduced the manual intervention needed to ensure quality data.
One question though - and it might just be me, but it makes the answer cell merge with the one below it - is that intentional? can it be stopped?
All solved in fact - thanks Andy
Glad it did what you wanted - as pointed out, it may not be entirely accurate since it assumes your working day has the same number of hours before and after midday, eg. 08:00 to 16:00.
How it works:
NETWORKDAYS(D64,D65)-2 : Calculate the number of working days between your start and end dates, subtract 2 to discard the start day and the end day.
1-(D64-INT(D64)) : Calculate the fraction of the start day, since dates in Excel are just numbers where the integer part is the number of days since 1.1.1900 and the decimal part is the fraction of the current day (ie: the time) then D64 - INT(D64) strips away the date. Subtracting from 1 gives you how much of the day is remaining.
D65-INT(D65)) : The same as above for the end day but this time you want how much of the day has elapsed.
SUM() : Add the 3 values together to get the total days. Could just have used '+' but the modular approach using SUM might make it easier to understand!
Elsewhere on the site
Last year, Finn McCann wrote an article about climbing El Capitan with his terminally ill father Seamus, who had been... Read more
Pete Whittaker has flashed the 32 pitch route Freerider 5.12d on El Capitan in Yosemite Valley over three days,... Read more
A fantastically versatile little pack; whether out running in the hills, hitting the trails on the bike or just running for the... Read more
Manchester Climbing Centre is showing Reel Rock’s Valley Uprising on Tuesday the 11th of November at... Read more