Calculate Future Date but don't want

  • Thread starter Thread starter Morocco Mole
  • Start date Start date
M

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).
 
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
 
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)
Click to expand...

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)
 
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).
Click to expand...

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
 
Back
Top