Subtracting time in date format

C

Collcat

Thank You
Good stuff.

I see one problem though. Due to me selecting 8 weeks for the example.

Lets say my due date is 11/9/04, 8 am and I have 3 days (72 hours) t
complete the assignment and I can't work on weekends.

According to the formula I should start on 11/6. Now 11/6 is on
Saturday!
So I need the formula to skip the weekend days when it is calculatin
backwards to the start date.

Any ideas?

// Coolca
 
A

anilsolipuram

try this


startdate=IF(B4<5,IF(WEEKDAY(B4)=7,A4-1-B4,IF(WEEKDAY(B4)=1,A4-2-B4,IF(B4-WEEKDAY(B4)<2,A4-2-B4,A4-B4))),IF(WEEKDAY(A4)=1,A4-2-INT(B4/5)*7-MOD(B4,5),IF(WEEKDAY(A4)=7,A4-INT(B4/5)*7-1-MOD(B4,5),A4-INT(B4/5)*7-MOD(B4,5))))


a4 is due date and b4 is number of working days needed to complete th
assignment
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel: counting times and dates during non-traditional hours 0
NETWORKDAYS 2
problem with time subtraction 7
Subtracting time in date format 1
Subtracting time...again 2
Subtracting time in date format 1
Help on multiple criteria for formula and return a date 1
How to Time time Diff for Completion of Activity (Exc Holiday List & Non Bus Hours) 2

Top