| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting a workbook with dates | Excel Discussion (Misc queries) | |||
| conditional formatting - compare 2 dates | Excel Discussion (Misc queries) | |||
| Conditional Formatting with Dates | Excel Worksheet Functions | |||
| conditional formatting overdue dates | Excel Discussion (Misc queries) | |||
| Conditional Formatting for dates | Excel Worksheet Functions | |||
Linear Mode
