Calculate Future Date but don't want

[Morocco Mole]

Hello.

I have some tasks that where the duration is in days (not workdays).

What formula would I used to calcuate a future date and if it lands on a
weekend to "round up" the future date to the following Monday (or round down
the Friday)

I know of the workday function but I believe the day input is the number of
workdays needed (where I have total days needed).

 

[Ashish Mathur]

Hi,

You could try this. I am sure there is a better way to solve this problem.

=IF(TEXT(G5+G6,"dddd")="Sunday",G5+G6+1,IF(TEXT(G5+G6,"dddd")="Saturday",G5+G6-1,G5+G6))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[MyVeryOwnSelf]

What formula would I used to calcuate a future date and if it lands on

a weekend to "round up" the future date to the following Monday (or
round down the Friday)

If the future date is in A1, here's one way to round up weekends to Monday:
=A1+IF(WEEKDAY(A1,2)>5,8-WEEKDAY(A1,2),0)

And to round down to Friday:
=A1-MAX(0,WEEKDAY(A1,2)-5)

 

[Teethless mama]

=A1+SUM((WEEKDAY(A1,2)={6,7})*{-1,1})

 

[Teethless mama]

Another one...

=A1+CHOOSE(WEEKDAY(A1,2),,,,,,-1,1)

 

[Morocco Mole]

Thank you all for the support!

 

[Ron Rosenfeld]

Hello.

I have some tasks that where the duration is in days (not workdays).

What formula would I used to calcuate a future date and if it lands on a
weekend to "round up" the future date to the following Monday (or round down
the Friday)

I know of the workday function but I believe the day input is the number of
workdays needed (where I have total days needed).

You can still use the Workday function, but only to add/subtract the last day.
And you can still use a list of holidays in case the Monday or Friday
represents a holiday, and you want to not have your answer on that day.

A1: Start Date
B1: Num of Days to add

To "round up" to Monday:

=WORKDAY(A1+B1-1,1)

To "round down" to Friday:

=WORKDAY(A1+B1+1,-1)
--ron