UKC

Hi All,

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:

=IF(P65=2, D65-D64,"")

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

In reply to G. Tiger, Esq.:

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.

In reply to G. Tiger, Esq.:

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

In reply to G. Tiger, Esq.:

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

In reply to G. Tiger, Esq.: I see what you are getting at - it's something that MS Project handles elegantly. Networkdays only works with weekdays that are not in your list of non-working days, but you need a way to define the length of the working day and thus the number of working hours to completion.

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!

In reply to AndyC:

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?

Thanks, GTE

In reply to G. Tiger, Esq.:

All solved in fact - thanks Andy

GTE

In reply to G. Tiger, Esq.:

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!