Subtracting business days from end of month

Hi - I am trying to come up with a formula that will calculate the estimated date for when my projected funding will run out. I have already come up with the number that needs to be subtracted form the end of a particular month, but I can not figure out how to subtract the amount of business days from the end date. I have attached the spreadsheet as an example, does anyone know of a simple formula to solve this issue?

Thanks,
GRDecker

Sample 1 Date.xlsx

does this help at all
http://www.ozgrid.com/forum/showthread.php?t=80985

Originally Posted by etaf

this should do it
=IF(WEEKDAY(C2,2)=7,C2-2,IF(WEEKDAY(C2,2)=6,C2-1,C2))
will not include holidays

This formula returned a date of 3/29/2013. What I need to figure out is what the last possible work day in the month of March, when I know that my funds will run out 6.32 days before the last possible workday. Cell B2 has the 6.32 figure.

so on your example , end of month is a Sunday 31/3
last business day that month is Friday
so if we go back by the value in B2 - in this case 6.32
will always go back to a business day
=IF(WEEKDAY(C2,2)=7,C2-2-B2,IF(WEEKDAY(C2,2)=6,C2-1-B2,C2-B2))

but these dates assume midnight and I notice you are using a fraction of the day - so did you want to calculate based on business hours at all

if the amount to go back varies - and falls on a weekend - do you then want to also go back to the previous business day ?

so calculating from midnight 6.32 days - goes back to a friday - BUT
from 17:00 - goes back to saturday

Hi - Thanks for the details. There is no need to go back to the hour, only a business day. So I will probably use the round function on my previous formula that gets me to 6.32. So lets just use whole numbers for this example.

so this should work
=IF(WEEKDAY(C2,2)=7,C2-2-B2,IF(WEEKDAY(C2,2)=6,C2-1-B2,C2-B2))

and what happens if that falls on a saturday or sunday ?

If the date falls on a Saturday, then I would need to move it back to the Friday.

so that would be reasonably easy by another nested IF testing the days
=IF(WEEKDAY(C19,2)=7,IF(WEEKDAY(C19-2-B19,2)=7,C19-4-B19,IF(WEEKDAY(C19-2-B19,2)=6,

if the weekday is a sunday - then take the 2 days back to friday and then the 6 days back
is the result of C19-2-B19 a weekend - if so go back another day or 2
had a quick play and lost the nested IFs - need to leave for a while now
this is wrong as I'm missing some of the false startments in the nested ifs
=IF(WEEKDAY(C19,2)=7,IF(WEEKDAY(C19-2-B19,2)=7,C19-4-B19,IF(WEEKDAY(C19-2-B19,2)=6,C19-3-B19,IF(WEEKDAY(C19,2)=6,IF(WEEKDAY(C19-1-B19,2)=7,C19-3-B19,IF(WEEKDAY(C19-1-B19,2)=6,C19-2-B19,C19-B19))))))

ughhhhh

Dang - that is one hell of a formula. I appreciate your help on this. I can't believe this would be that complex just to figure what seems such a simple question.

yep, i have stopped for the night working on it , as i lost the plot - so will look again tomorrow - i have simplified the formula a little using AND/OR

there maybe a simpler way - just the way my mind was working
test to see if the day is a Sunday and if the result is a sunday
then if a combination of Sunday/saturday
then if a saturday and result is a saturday - but sleeping on it may come up with a much better solution
another none working formula
=IF(AND(WEEKDAY(C19,2)=7,WEEKDAY(C19-2-B19,2)=7),C19-4-B19,IF(OR(AND(WEEKDAY(C19,2)=7,WEEKDAY(C19-2-B19,2)=6),AND(WEEKDAY(C19,2)=6,WEEKDAY(C19-2-B19,2)=7), AND(WEEKDAY(C19,2)=7,WEEKDAY(C19-1-B19,2)=7),AND(WEEKDAY(C19,2)=6,WEEKDAY(C19-1-B19,2)=6)),C19-3-B19,IF(AND(WEEKDAY(C19,2)=6,WEEKDAY(C19-1-B19,2)=6),C19-1-B19,IF(WEEKDAY(C19-2-B19)=7,C19-2-B19,IF(WEEKDAY(C19-1-B19,2)=6,C19-1-B19,C19-B19)))))

much simpler - dah!!!
I have just worked through all possible variations of days starting and stopping and the required result and that works
=WORKDAY(WORKDAY(C2+1,-1)-B2+1,-1)
perfect on my example await OP reply - but works on my sheet , been at this a few hours myself

thanks daddylonglegs i learnt a new function I was not aware of

much simpler - dah!!!
I have just worked through all possible variations of days starting and stopping and the required result and that works
=WORKDAY(WORKDAY(C2+1,-1)-B2+1,-1)
perfect on my example await OP reply - but works on my sheet , been at this a few hours myself

thanks daddylonglegs i learnt a new function I was not aware of, or at least how to use it properly