Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Morning,
Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Hi,
Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Hi
with start date in A1 =A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1) will give the weekday that is closest to the 15th of the month. -- Regards Roger Govier "Sunnyskies" wrote in message ... Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Is your date a formatted as a date?
-- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Rogers formula uses exactly same logic as formula I gave you so expect same
issue will apply (but I think it's a slick way of doing it). Suspect your date isn't formatted as date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Roger Govier" wrote: Hi with start date in A1 =A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1) will give the weekday that is closest to the 15th of the month. -- Regards Roger Govier "Sunnyskies" wrote in message ... Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Yup, format-date-type-*2001-03-14
"Jon von der Heyden" wrote: Is your date a formatted as a date? -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Hi Jon
Your posting assumed that the OP had already added the offset to the date. from their response returns the same date as my opening date I assumed that the date in the source cell was the 1st of the month. Adding +14 to each occurrence of B3 in your formula, would produce the correct result. -- Regards Roger Govier "Jon von der Heyden" wrote in message ... Rogers formula uses exactly same logic as formula I gave you so expect same issue will apply (but I think it's a slick way of doing it). Suspect your date isn't formatted as date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Roger Govier" wrote: Hi with start date in A1 =A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1) will give the weekday that is closest to the 15th of the month. -- Regards Roger Govier "Sunnyskies" wrote in message ... Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Another way
=workday(A1+13,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi with start date in A1 =A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1) will give the weekday that is closest to the 15th of the month. -- Regards Roger Govier "Sunnyskies" wrote in message ... Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Hi Bob
That's fine apart from April and July where the result is the 17th, which is further away from the 15th than the Friday (14th). As the OP said it could be the Friday before the weekend, I took it to mean he wanted the date which was the closest to the 15th. -- Regards Roger Govier "Bob Phillips" wrote in message ... Another way =workday(A1+13,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi with start date in A1 =A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1) will give the weekday that is closest to the 15th of the month. -- Regards Roger Govier "Sunnyskies" wrote in message ... Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in middle of month
Okay, if you are going to make me work for it
=workday(A1+14,(WEEKDAY(A1)=1)-(WEEKDAY(A1)=7)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob That's fine apart from April and July where the result is the 17th, which is further away from the 15th than the Friday (14th). As the OP said it could be the Friday before the weekend, I took it to mean he wanted the date which was the closest to the 15th. -- Regards Roger Govier "Bob Phillips" wrote in message ... Another way =workday(A1+13,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi with start date in A1 =A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1) will give the weekday that is closest to the 15th of the month. -- Regards Roger Govier "Sunnyskies" wrote in message ... Sorry Jon does not work, returns the same date as my opening date. "Jon von der Heyden" wrote: Hi, Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3)) Where B3 houses the date. -- Email: subst1tut3 numb3rs for l3tt3rs... "Sunnyskies" wrote: Morning, Scenario. Have got an opening date say the 2006-10-01 now I want another date - closing date the 2006-10-15 but if this closing date falls into a weekend then I want the next working day to be shown. The closing date must always be around the 15th of the month, but not on weekends (if so it can then be on the Monday or preceeding Friday) as mentioned above. My formula for closing date is currently =EOMONTH(C6,0) with is showing 2006-10-31, how can I modify this to meet my requirement mentioned above. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pick up date, month and year from a date | Excel Discussion (Misc queries) | |||
Matching month part of date only | Excel Discussion (Misc queries) | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
GETTING MONTH FROM A DATE | Excel Discussion (Misc queries) | |||
HELP with this function | Excel Worksheet Functions |