Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 107
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 107
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 107
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pick up date, month and year from a date vikkam Excel Discussion (Misc queries) 4 July 27th 06 04:27 AM
Matching month part of date only RGB Excel Discussion (Misc queries) 4 July 17th 06 02:44 PM
Date arithmetic: adding 1 month to prior end of month date manxman Excel Worksheet Functions 2 July 14th 06 09:29 PM
GETTING MONTH FROM A DATE [email protected] Excel Discussion (Misc queries) 8 October 6th 05 01:26 AM
HELP with this function Jay Excel Worksheet Functions 7 May 24th 05 06:45 PM


All times are GMT +1. The time now is 03:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"