Round dates to weekdays

  • Thread starter Thread starter Nan
  • Start date Start date
N

Nan

Is there a way that Excel can recognize if a date is a weekend and round it
to the nearest weekday?
 
Using another cell and assume your date is in A1 ---> =IF
(MOD(A1,7)<2,A1+2-MOD(A1,7),A1)
 
=A10+(WEEKDAY(A10,2)>5)+(WEEKDAY(A10)>6)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Is there a way that Excel can recognize if a date is a weekend and round it
to the nearest weekday?
Click to expand...

One way is to use a formula (with your date in A1):

=A1-(WEEKDAY(A1)=7)+(WEEKDAY(A1)=1)
--ron
 
One way...

A2 = some date

=A2+CHOOSE(WEEKDAY(A2),1,0,0,0,0,0,-1)

A Saturday date gets reduced to Friday's date and a Sunday date gets
advanced to Monday's date.
 
Thank you all for your great (and FAST!) replies. I know at least one will
do the trick for me!
 
I have a chart that is based on different formulas for each column. I want to keep the formulas I have and make sure the chart only displays weekdays.

I've found the formula

=IF
(MOD(A1,7)<2,A1+2-MOD(A1,7),A1)

But I don't know how to add that to my pre-existing formulas.

my existing formula for all rows in column A is =A#+5
in column B, my formula is =B#+3
etc. Each column is different.

Can you help?




dhstei wrote:

Using another cell and assume your date is in A1 ---> =IF
21-May-08

Using another cell and assume your date is in A1 ---> =I
(MOD(A1,7)<2,A1+2-MOD(A1,7),A1

:

Previous Posts In This Thread:

Round dates to weekdays
Is there a way that Excel can recognize if a date is a weekend and round i
to the nearest weekday
-
TIA, Nan

Using another cell and assume your date is in A1 ---> =IF
Using another cell and assume your date is in A1 ---> =I
(MOD(A1,7)<2,A1+2-MOD(A1,7),A1

:

=A10+(WEEKDAY(A10,2)>5)+(WEEKDAY(A10)>6)-- ---HTHBob(there's no email, no
=A10+(WEEKDAY(A10,2)>5)+(WEEKDAY(A10)>6

-
--
HT

Bo

(there is no email, no snail mail, but somewhere should be gmail in my addy)

Re: Round dates to weekdays
One way is to use a formula (with your date in A1)

=A1-(WEEKDAY(A1)=7)+(WEEKDAY(A1)=1
--ron

One way...
One way..

A2 = some dat

=A2+CHOOSE(WEEKDAY(A2),1,0,0,0,0,0,-1

A Saturday date gets reduced to Friday's date and a Sunday date get
advanced to Monday's date

-
Bif
Microsoft Excel MVP

Thank you all for your great (and FAST!
Thank you all for your great (and FAST!) replies. I know at least one wil
do the trick for me
-
TIA, Na

:

Maybe a little simpler...
Maybe a little simpler..

=A1-(MOD(A1,7)=0)+(MOD(A1,7)=1

Rick

EggHeadCafe - Software Developer Portal of Choice
Scriptless ASP Progress Indicator
http://www.eggheadcafe.com/tutorial...5-f5cebaba13a8/scriptless-asp-progress-i.aspx
 
Back
Top