counting time between change in status in excel

slingerz

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!!

invaderzimirl

if you just subtrace and format the result at a number you should get what you need ie

see attached snip nfrom excel format answer to 4 places or decimal

invaderzimirl

as for the weekend removal i think there is a workdays formula like that but ill get back to you on that later need to run to a meeting

slingerz

thanks for that, not sure what the subtrace you have mentioned means but it certainly looks close to what is required.

I will have a list of the dates that I would need to exclude from the calculation each time it is needed if that helps? i have tried the networkdays formula before but not to much luck

invaderzimirl

ok i think i have what you wnat now :-)

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

where C7 is start date C8 is end date, B2:B5 is exclude list. the last bit extracts the time element from each and adds them together(subtracts if needed) then added back into the orgional network days fuction.

there is a -1 at the end to account for the day over lap, ill see if i can clean it up some more

i have uploaded a screen grab of it and the xls doc also

hope this helps



seems to work let me know if you need any more

slingerz

thanks for this, i'm going to test it out this afternoon and let you know if its successful!!

thanks again!

invaderzimirl

ive looked at it and it will work for the mon-fri but it excludes all sat/sun. ill have another look at why its not taking the weekends into accountshould be something small.


no prob :-) we all here to help each other

slingerz

invaderzimirl wrote: »

ive looked at it and it will work for the mon-fri but it excludes all sat/sun. ill have another look at why its not taking the weekends into accountshould be something small.


no prob :-) we all here to help each other

hey have been trying to amend this formula to include sat/sun and just exclude the dates that i provide.

have you had any luck in amending this formula?

slingerz

by swapping the days in for network days it hasnt really solved my problem.

i have attached an example of what i have already but i need to exclude certain dates from the calc, the networkdays option works in the example as there are no weekends involved but that is not always the case

please have a look and see if you can help me with this

thanks