Weekends

[Bob]

I am trying to set up a function that will check the following

I am using Excel XP Pro

I have a date in cell F9. It places a date in F1 that will be 7 days
earlier. (This works fine)

Question:
If the date in F1 is a Saturday or Sunday, I need it to put the date of
Monday in that cell.

Does any one know the formula for this.

Thanks in advance.

bob
(e-mail address removed)

 

[Ron Coderre]

Perhaps this?

F1: =F9+CHOOSE(WEEKDAY(F9,2),0,0,0,0,0,2,1)-7

But....if you have the Analysis ToolPak add-in installed:
F1: =WORKDAY(F9,-5)

Does that help?

 

[Peo Sjoblom]

One way

=IF(WEEKDAY(F9,2)>5,F9-MOD(F9+5,7),F9-7)

 

[vezerid]

=F9-7+2*(WEEKDAY(F9)=6)+(WEEKDAY(F9)=7)

HTH
Kostis Vezerides

 

[barbara.reinhardt]

I am trying to set up a function that will check the following

I am using Excel XP Pro

Bob,

I'd probably do it this way

=IF(WEEKDAY(F1,2)>=6,F1+8-WEEKDAY(F1,2),F1)

HTH,
Barb Reinhardt

 

[Bob]

Thanks for the info

Works perfect

Perhaps this?

F1: =F9+CHOOSE(WEEKDAY(F9,2),0,0,0,0,0,2,1)-7

But....if you have the Analysis ToolPak add-in installed:
F1: =WORKDAY(F9,-5)

Does that help?
----------------------
Regards,

Ron
Microsoft MVP (Excel)

 

[Rick Rothstein \(MVP - VB\)]

I am trying to set up a function that will check the following

I am using Excel XP Pro

I have a date in cell F9. It places a date in F1 that will be 7 days
earlier. (This works fine)

Question:
If the date in F1 is a Saturday or Sunday, I need it to put the date of
Monday in that cell.

Does any one know the formula for this.

While I recognize conciseness in a formula is not the ultimate goal,
especially if it impacts readability, I decided, as a personal exercise
only, to see what the most concise formula I could come up with to do what
you asked. Now, I don't recommend you use this in production as its purpose
cannot be easily discerned by looking at it (and besides, you already have a
few really good formulas to chose from that others have already posted); but
I managed to create two formulas that both do what you asked, each composed
of a total of 33 keystrokes (including the equal sign)... I don't think a
more concise formula exists (remember now, I did this strictly as a personal
challenge exercise; however, I figured others might find them interesting
also). Anyway, with all of that said, here are the two formulas I found...

=F9+7+(2-MOD(F9,7))*(MOD(F9,7)<2)

=F9+7+(MOD(F9,7)<2)+(MOD(F9,7)=0)

Rick

 

[Harlan Grove]

One way

=IF(WEEKDAY(F9,2)>5,F9-MOD(F9+5,7),F9-7)

....

Only works in the 1900 date system.

 

[Ron Rosenfeld]

While I recognize conciseness in a formula is not the ultimate goal,
especially if it impacts readability, I decided, as a personal exercise
only, to see what the most concise formula I could come up with to do what
you asked. Now, I don't recommend you use this in production as its purpose
cannot be easily discerned by looking at it (and besides, you already have a
few really good formulas to chose from that others have already posted); but
I managed to create two formulas that both do what you asked, each composed
of a total of 33 keystrokes (including the equal sign)... I don't think a
more concise formula exists (remember now, I did this strictly as a personal
challenge exercise; however, I figured others might find them interesting
also). Anyway, with all of that said, here are the two formulas I found...

=F9+7+(2-MOD(F9,7))*(MOD(F9,7)<2)

=F9+7+(MOD(F9,7)<2)+(MOD(F9,7)=0)

Rick

Well, if you have Excel 2007, or if you have the ATP installed, you could use:

=WORKDAY(F1+6,1)
--ron

 

[Ron Rosenfeld]

While I recognize conciseness in a formula is not the ultimate goal,
especially if it impacts readability, I decided, as a personal exercise
only, to see what the most concise formula I could come up with to do what
you asked. Now, I don't recommend you use this in production as its purpose
cannot be easily discerned by looking at it (and besides, you already have a
few really good formulas to chose from that others have already posted); but
I managed to create two formulas that both do what you asked, each composed
of a total of 33 keystrokes (including the equal sign)... I don't think a
more concise formula exists (remember now, I did this strictly as a personal
challenge exercise; however, I figured others might find them interesting
also). Anyway, with all of that said, here are the two formulas I found...

=F9+7+(2-MOD(F9,7))*(MOD(F9,7)<2)

=F9+7+(MOD(F9,7)<2)+(MOD(F9,7)=0)

Rick

Misread:

Should be:

=WORKDAY(F1-8,1)
--ron