Excel help

elblackerino

Hi Folks,
I need to count turn around time on dates, that would exclude weekends/Irish bank hols.

I have started by creating a database manually with 1's against working dates, 0's against others.

Is there anyway to incorporate this into a basic +close date - opening date = turn around days? e.g. instead of 18/06/2012 - 15/06/2012 = 3, the value returned is 1.

Is there an IF/sum/count/range function I dont know about or will this be IF logic I need to figure out to write manually?

If its manually logic, any suggestions?

Any help would be great!
Thanks,
Michael

FSL

One way would be to have the 1's and 0's in a column and use a macro to get the day number of the start and end date then sum the cells in your 1's and 0's column from the start day number to the end day number.

For instance if your day from was in say column M and day to was in Column N and your list of 1's and 0's in column A then your macro could set column O to the formula = sum($Ax:$Ay) where x was the day number of the date in Column M and y was the day number of the date in column N.

You would of course have to allow for the dates to span a year end.

Sup08

Hope this helps



or use this way without cell reference, the code in the picture means you can actually put a date in the cell if you want by removing the "-Ay" at the end of the formula.

=IF(B41="Sunday",4,IF(B41="Saturday",5,IF(B41="Friday",6,IF(C41="Yes",4,3))))

Sup08

Sorry, I had left out that Friday should only be six when Monday is a bank holiday.
So the if statement should be this.

=IF(B2="Sunday",4,IF(B2="Saturday",5,IF(AND(B2="Friday",C5="Yes"),6,IF(B2="Friday",5,IF(C2="Yes",4,3)))))

elblackerino

Thanks folks, I'll give it a bash now!