Select Date based on date value

S

salooha

Is there a formula where I could select a date based on a condition?

here is a scenario of what I want the formula to do:

I have 3 dates:

start date,end date, and invoice date, let's assume that the start dat
is 9/7/2005, end date is 10/6/2005 and invoice date is 10/8/2005. no
even though the invoice was sent on 10/8/2005, I would like to identif
this month as september because the bulk of the month fell on september
I guess the rule would be(per example above):

per example above:
if starting month >= 09/20 then select 10/2005
else month < 9/20 then select 9/2005

so if the 20th of the month in the starting date passed then displa
the follwoing month, if not display current month. this will hel
identify the months not based on the invoice date but based on th
month that had the bulk of the activity.

I hope this make sense, please post reply or email me a
[email protected]

thanks a lot for your help...

sala
 
R

Ron Rosenfeld

Is there a formula where I could select a date based on a condition?

here is a scenario of what I want the formula to do:

I have 3 dates:

start date,end date, and invoice date, let's assume that the start date
is 9/7/2005, end date is 10/6/2005 and invoice date is 10/8/2005. now
even though the invoice was sent on 10/8/2005, I would like to identify
this month as september because the bulk of the month fell on september.
I guess the rule would be(per example above):

per example above:
if starting month >= 09/20 then select 10/2005
else month < 9/20 then select 9/2005

so if the 20th of the month in the starting date passed then display
the follwoing month, if not display current month. this will help
identify the months not based on the invoice date but based on the
month that had the bulk of the activity.

I hope this make sense, please post reply or email me at
[email protected]

thanks a lot for your help...

salah
Click to expand...


=TEXT(DATE(YEAR(StartDate),MONTH(StartDate)
+(DAY(StartDate)>=20),1),"mm/yyyy")

or, if you need to do calculations on this date:

=DATE(YEAR(StartDate),MONTH(StartDate)+(DAY(StartDate)>=20),1)

and format the cell as mm/yyyy
--ron
 
R

Roger Govier

Hi

One way
=Date(Year(A1),Month(A1)+(--(Day(a1)>=20),1)
Format>Cells>Number>Custom mm/yyyy


Regards

Roger Govier
 
S

salooha

Thanks a lot Guys...

here is what I came up with also, which one of the responses was clos
to it....


Code
-------------------
=+IF(DAY(B2)>=20,MONTH(C2),MONTH(B2)
-------------------


I appreciate your help..

thanks,
sala
 
Top