formatting dates
 

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

One way:

=IF(A1="","",A1+IF(OR(WEEKDAY(A1)=6,WEEKDAY(A1)=7) ,3,1))

Regards

Trevor


"CC" wrote in message
...
For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

Custom formatting as "dddd".
Your results will be shown as Monday, Tuesday, etc.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================
"CC" wrote in message
...
For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

Just another way:

=IF(A1="","",A1+3^(WEEKDAY(A1,2)=5))

If A1 will always be a weekday then it can be slightly shortened to:

=IF(A1="","",A1+3^(WEEKDAY(A1,2)=5))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"CC" wrote in message
...
For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

Thanks to all for the speedy replies! It is appreciated very much! -CC

"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

formatting dates
 

If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson

formatting dates
 

Dave, thanks again

Greg
Dave Peterson wrote:
If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson

formatting dates- getting an error
 

Hi Dave.....I tried your formula (a form of it), but I am still getting an
error:

=IF($E$42="","",WORKDAY($E$42+I11))

E42 is the start date and I11, I12, etc. are the cells that I have set aside
to adjust the amount of days I am working back. So for example, if E42 is
Monday July 10, I want the date above to be Friday July 7 (so I set I11 to be
-1).

FYI, I checked the analysis toolpak and analysis toolpak vba in the add-ins.

Any help is appreciated. Thanks! -CC



"Dave Peterson" wrote:

If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson

formatting dates- getting an error
 

Try this:

=IF($E$42="","",WORKDAY($E$42,I11))

Notice the comma in the WORKDAY section of the formula. The second argument
of the WORKDAY function is for the number of workdays to be added/subtracted.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:


Hi Dave.....I tried your formula (a form of it), but I am still getting an
error:

=IF($E$42="","",WORKDAY($E$42+I11))

E42 is the start date and I11, I12, etc. are the cells that I have set aside
to adjust the amount of days I am working back. So for example, if E42 is
Monday July 10, I want the date above to be Friday July 7 (so I set I11 to be
-1).

FYI, I checked the analysis toolpak and analysis toolpak vba in the add-ins.

Any help is appreciated. Thanks! -CC



"Dave Peterson" wrote:

If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson

formatting dates- getting an error
 

SUCCESS! Thanks Ron. -CC

"Ron Coderre" wrote:

Try this:

=IF($E$42="","",WORKDAY($E$42,I11))

Notice the comma in the WORKDAY section of the formula. The second argument
of the WORKDAY function is for the number of workdays to be added/subtracted.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:


Hi Dave.....I tried your formula (a form of it), but I am still getting an
error:

=IF($E$42="","",WORKDAY($E$42+I11))

E42 is the start date and I11, I12, etc. are the cells that I have set aside
to adjust the amount of days I am working back. So for example, if E42 is
Monday July 10, I want the date above to be Friday July 7 (so I set I11 to be
-1).

FYI, I checked the analysis toolpak and analysis toolpak vba in the add-ins.

Any help is appreciated. Thanks! -CC



"Dave Peterson" wrote:

If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson

formatting dates- getting an error
 

You're welcome....(but, Dave Peterson deserves the credit.)

***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

SUCCESS! Thanks Ron. -CC

"Ron Coderre" wrote:

Try this:

=IF($E$42="","",WORKDAY($E$42,I11))

Notice the comma in the WORKDAY section of the formula. The second argument
of the WORKDAY function is for the number of workdays to be added/subtracted.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:


Hi Dave.....I tried your formula (a form of it), but I am still getting an
error:

=IF($E$42="","",WORKDAY($E$42+I11))

E42 is the start date and I11, I12, etc. are the cells that I have set aside
to adjust the amount of days I am working back. So for example, if E42 is
Monday July 10, I want the date above to be Friday July 7 (so I set I11 to be
-1).

FYI, I checked the analysis toolpak and analysis toolpak vba in the add-ins.

Any help is appreciated. Thanks! -CC



"Dave Peterson" wrote:

If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson

formatting dates- getting an error
 

A team effort by all!

Ron Coderre wrote:

You're welcome....(but, Dave Peterson deserves the credit.)

***********
Regards,
Ron

XL2002, WinXP

"CC" wrote:

SUCCESS! Thanks Ron. -CC

"Ron Coderre" wrote:

Try this:

=IF($E$42="","",WORKDAY($E$42,I11))

Notice the comma in the WORKDAY section of the formula. The second argument
of the WORKDAY function is for the number of workdays to be added/subtracted.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:


Hi Dave.....I tried your formula (a form of it), but I am still getting an
error:

=IF($E$42="","",WORKDAY($E$42+I11))

E42 is the start date and I11, I12, etc. are the cells that I have set aside
to adjust the amount of days I am working back. So for example, if E42 is
Monday July 10, I want the date above to be Friday July 7 (so I set I11 to be
-1).

FYI, I checked the analysis toolpak and analysis toolpak vba in the add-ins.

Any help is appreciated. Thanks! -CC



"Dave Peterson" wrote:

If you have the analysis toolpak loaded (tools|Addins), you can use a range that
contains that list of holidays (give it a nice name):

=IF(A1="","",WORKDAY(A1,1,holidays))



GregR wrote:

Is there a way to incorporate holidays into this formula? TIA

Greg

Ron Coderre wrote:
Another way...a bit more compact:

For A1 = any StartDate (even a Sat or Sun)

A2: =A1+MATCH(MAX(WEEKDAY(A1+1,2),5),{5,7,6},0)
Copy A2 down as far as needed

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If you want to list consecutive workdays, beginning with the value in A1, try
this:

A1: (StartDate)
A2: =IF(A1="","",A1+MATCH(TRUE,(WEEKDAY(A1+{1,2,3},2)< 6),0))
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CC" wrote:

For the following formula,

=IF(A1="","",A1+1)

note: where A1 is a start date

How do I format it for weekdays only?

I'm trying to create a schedule template that will work backwards.

Thanks in advance for your help.

--

Dave Peterson


--

Dave Peterson