I need to count the number of days to 4 decimal places for a change in status. For example, the start and end dates would be in the following format:

28/10/2013 13:39
11/11/2013 11:51



to give an answer like 9.925

The weekend dates would also need to be excluded however not every Saturday and Sunday as they may be worked from occasion so i would have a manual tab of the dates that I would like to exclude. I have tried using the networkdays formula and while it works it only returns whole numbers which is not completely fair or reflective on the actual time taken by the various departments.

Anybody able to share some expertise on this? If you need more info please let me know!!

Thanks in advance!!

Please refrain from cross posting without links. Read the link in my sig to understand why
http://www.excelforum.com/excel-form...ml#post3467158
http://www.mrexcel.com/forum/excel-q...ge-status.html

also cross posted at ozgrid and excelguru

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Swooshy, I'm not sure I follow. Suppose you figure out an easy way to calculate dates. 2013-10-28 13:39 to 2013-11-11 11:51, that's 13.9250 days; then you consult that manual tab of dates you want to omit, say one Saturday and two Sundays, and you come up with 10.9250 days. Is that a useful result? I don't see how.

It seems to me that if you don't want to count every calendar day as one day—which is reasonable enough—then you must define for yourself what you do count as a day.

For example, how will 8 hours work for you? If a person works 16 hours on Wednesday, will you count that as two days? If he works four 10-hour days and then goes home Friday, is that five days?

If so, then it's not so hard; you count up the hours a person worked, divide by 8 and presto!, a day count. But if not that, then what exactly?

Or maybe that's what you're asking us, for some advice on how to define a "day"?

Perhaps something along the lines of the attached file.

Attached Files

Calculating turnaround times in excel.xlsx (15.1 KB, 62 views)

thanks for that, it does indeed look good, just to confirm that it will include saturday and sundays and allow me to exclude only the dates that i want?

what i currently have is

=NETWORKDAYS(C7,C8,B2:B5)+(TEXT(C8,"HH:MM")-TEXT(C7,"HH:MM"))-1

however this just does monday/friday and excludes saturday and sundays anyway which i do not want it to do

Hi,
I guess you mean the number of weekdays (or working days) excluding Saturdays and Sundays. So start a spreadsheet and put the start date (28/10/2013) in A2 and the end date (11/11/2013) in B2.

Then paste the formula below into C2:

=B2-A2-2*INT ((B2-A2+WEEKDAY (A2, 3))/7)-MAX (0, WEEKDAY (B2, 3)-4


Thanks, I hope it works for you.....

i want the number of working days but that can include some Saturday's and Sundays, I only want to exclude the set list of dates that I will have in another tab.

Can you walk me through the formula that you posted so that i can understand what is going on there if you think it is something that will answer my question

the idea is to calculate turn around times which would typically just be the normal working week so weekends excluded however the industry demands that certain weekend days be worked particularly coming up to EOQ so the network days does not accurately fit the needs.

A day is typically 1 set of 24 hours due to the fact that the item may be worked upon by various groups around the globe. this is why a simple hours calculation is not readily appropriate in this regards as the department may be working beyond normal working hours on occasions

Why don't you post a sample worksheet with your expected result, rather then let everyone second guess what you require!

Hi everyone,

Attached is what i have at present which is using NETWORKDAYS which is not accurate. I have on a second tab the dates that I wish to exclude from the calculation.

Can anyone help me with this calc so that I can exclude the specific dates and still achieve the results in the attached examlpe (example works as it was all standard weekends that where necessary to exclude)

Attached Files

Sample Date Calc File.xlsx (16.2 KB, 24 views)

Swooshy, you may or may not thank me for this, but before I could understand what you were doing, I had to break it down into pieces and then simplify it. The attached workbook is not the answer to your question; I just added a sheet "Simple" which shows how "Sample" might look, doing the same thing yet being easier to follow. All your logic is in there, it's just that by adding some helping columns and using a few absolute references, the formulae are a lot shorter.

Attached Files

x.xlsx (22.9 KB, 38 views)

You posted a variation on this question at MrExcel here

I suggested using NETWORKDATS.INTL function (available in Excel 2010 and later) because that function will allow you to count all days except those you list as exceptions, so matching my suggestion to your workbook perhaps try variations on this formula

=NETWORKDAYS.INTL(C2,D2,"0000000",'Dates to exclude'!A$1:A$8)-1+MOD(D2,1)-MOD(C2,1)

....although I'm not really clear what result you are expecting when D2 < C2

i responded to barry's post at http://www.mrexcel.com/forum/excel-q...ml#post3640053

i am not certain of the existing formula myself as i have inherited it just that the basic requirement is to count the time that it takes for the status to flip for those listed in the example.

I will review BobBridges proposal and update further

Hi BobBridges

Your example seems to tie out with what is required alright but unfortunately my excel is not strong enough to understand what is going on there.

would you mind updating your example with some comments so that I can follow what is going on there? The thing is there is in excess of 10K rows in the report that I am using with numerous opportunity numbers so the formula would need to flexible to allow the drag down functionality to work across those. this may be a given but i just need to make sure!

Your formulae in columns G and N-U were pretty complex, so to see what was going on I broke them down into pieces, then rebuilt them using helping columns at the right. For instance, the formula in Sample!H2 is To break that down and make it easier to understand, I looked at it this way:Right away I see several things I can do to make this more comprehensible:

1) This one formula is calculating NETWORKDAYS(C2,D2) three times. For a few records that may not matter, but it's doing that not just in column G but in 8 other columns as well, and you say there are >10K rows. That's making Excel do a lot of repetition. So let's let each row make the calculation just once. I put =NETWORKDAYS($C2,$D2) in column Y, and let H2 refer to that instead:2) Likewise it's calculating D2-C2 three times. Mere subtractions aren't as time-consuming as calls to a function such as NETWORKDAYS; still, let's put =$D2-$C2 in column X, simplifying H2 further: 3) One more thing: The two formulae Y2-1+X2 and Y2-1+(X2-ROUND(X2,0)) are similar; it's only that last part that varies. You want H2 to start out as Y2-1+X2 all the time, but then you want to subtract ROUND(X2,0) only if Y2>0. Well, you can express that by moving the inner IF out to that part of the equation: (I've a notion that even Y2-1+X2-ROUND(X2,0) can be simplified somehow, but truthfully I got lazy and didn't look closely at it.)

So I put "=Y2-1+X2-IF(Y2<=1,0,ROUND(X2,0))" in column W and made H2 be simply "=IF(AND($E2="Booking Status",$G2="Booked"),$W2,0)". I did the same for the rest of column H, and all of column N.

Now, columns O-U were like this in basic form but they replaced every reference to C2 with a VLOOKUP on C2. VLOOKUPs are a bit more expensive, in terms of time required to execute, and for >10K rows you may even have noticed your worksheet having to think a bit when you made changes. That's six identical VLOOKUPs in each of seven cells on the row. I combined those 42 VLOOKUPs into one ("=VLOOKUP($M6,$J:$K,2,FALSE) in column AD. Then I made three helping columns AA through AC that are identical to those in columns W through Y. That means that the VLOOKUP could be executed just once for each row (instead of 42 times), and the same for NETWORKDAYS (instead of 6 times), and so on. So each of those long formulae in columns O through U are reduced to something like$AA2 expands to, and AC2 and AB2 expand further ... you get the idea, I hope?