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

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)


"Bob" wrote in message
...
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

One way

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



--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
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

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

HTH
Kostis Vezerides

On Jun 7, 7:03 pm, "Bob" wrote:
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

On 7 Jun, 12:03, "Bob" wrote:
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

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

Thanks for the info

Works perfect

"Ron Coderre" wrote in message
...
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)


"Bob" wrote in message
...
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

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

"Peo Sjoblom" wrote...
One way

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

Only works in the 1900 date system.

On Fri, 8 Jun 2007 00:49:59 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

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

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

On Fri, 8 Jun 2007 00:49:59 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Misread:

Should be:

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