How can I create a formla using date ranges

D

Digital2k

How can I create a formula that will do this: = IF cell A2 ="m" and if cell
D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m" and D2
is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!
 
R

Ryan.Chowdhury

If June / July is your only concern, then:

=if( A2 <> "m", ___whatever happens if A2 is not m____, if(
and(D2>38854,D2<=388888), "June", "July"))

Sounds like you want everything before the 19th in one month and
everything after in the next so:

=if( A2 <> "m", ___whatever happens if A2 is not m____, if(
day(D2)<19, month(D2), month(d2)+1))
 
F

Franz Verga

Nel post *Digital2k* ha scritto:
How can I create a formula that will do this: = IF cell A2 ="m" and
if cell D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if
A2 = "m" and D2 is between 6/21/06 and 7/18/06 than E2 = "July" and
so on? This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!
Click to expand...


Maybe it should be better what are your needs, why do you need a such
formula, so we can help you better.

Help us to help you... :)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
D

Dav

How can I create a formula that will do this: = IF cell A2 ="m" and if
cell
D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m"
and D2
is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")
*IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!

if((and(a2="m",d2>=38854,d2<=38888),"June",if(and(a2="m",d2>=38889,d2<=38916),"July","unknown"))

but a vlookup could be easier if you are going to expand to other
months
=if(a2="m",vlookup(d2,a1:b10,2),"unknown")

where column a has the start dates of each of your ranges and b the
corresponding month

Regards

Dav
 
D

Digital2k

I'm in the Insurance business and when I sell a product I have an expected
date that I think it will get approved and get paid. One of the products,
product "M" must get paid before the third Wednesday of the month if not
then it will go the following month.

I want to create a spreadsheet that will automatically make the adjustment
by changing the month paid if the product is product "m" and the expected
date paid is after the third Wednesday of the month. How can I create a
formula that will do this? please help and thank you in advance.
 
F

Franz Verga

Nel post *Digital2k* ha scritto:
I'm in the Insurance business and when I sell a product I have an
expected date that I think it will get approved and get paid. One of
the products, product "M" must get paid before the third Wednesday of
the month if not then it will go the following month.

I want to create a spreadsheet that will automatically make the
adjustment by changing the month paid if the product is product "m"
and the expected date paid is after the third Wednesday of the month.
How can I create a formula that will do this? please help and thank
you in advance.
Click to expand...

Maybe I found a starting point:

=IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))

where A30 is the cell in which you have your date and B30 is the cell with
product.

The formula returns the number of the month (6 for June, 7 for July, and so
on), you can format the cell to display the name by menu Format, Cells,
select the tab Number, choose Custum under Category, type mmm or mmmm if you
want the short or the long name of the month.

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
F

Franz Verga

Nel post *Franz Verga* ha scritto:
Nel post *Digital2k* ha scritto:


Maybe I found a starting point:

=IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))

where A30 is the cell in which you have your date and B30 is the cell
with product.

The formula returns the number of the month (6 for June, 7 for July,
and so on), you can format the cell to display the name by menu
Format, Cells, select the tab Number, choose Custum under Category,
type mmm or mmmm if you want the short or the long name of the month.
Click to expand...


Just a little correction...

To have just the name of month you need to switch to this formula:

=DATE(YEAR(A30),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),1)

and format as I wrote above.

If, instead you want to use this month's number to construct a new date, use
this one:

=DATE(YEAR(A31),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),DAY(A31))

where, maybe, in A31 you can have another date...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
B

bplumhoff

Hello,

=IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
else")

HTH,
Bernd
 
F

Franz Verga

Nel post *Franz Verga* ha scritto:
Nel post *Franz Verga* ha scritto:



Just a little correction...

To have just the name of month you need to switch to this formula:

=DATE(YEAR(A30),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),1)

and format as I wrote above.

If, instead you want to use this month's number to construct a new
date, use this one:

=DATE(YEAR(A31),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),DAY(A31))

where, maybe, in A31 you can have another date...
Click to expand...

I think I was too sleepy...

This *do* the job:

=IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2))


Here in A2 you have the date to test and in B2 the product.

As above this formula returns just the number of a month, so if you want to
see it in plain text, you have to trasform it in a date and the format
properly, so use:

=DATE(DATE(YEAR(A2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),1)

or

=DATE(DATE(YEAR(C2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),DAY(C2))

if you want just the name of the month or a new date (in C2 you can have a
date from which you can take the year and/or the day)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
D

Digital2k

Thank you very much for this formula. It works perfect for June. But as I
use August, September and later, It does not change the month on the third
Wednesday of the month. This is the closes as anyone has come to making this
happen. if anyone can help me I would greatly appreciated.

Once again when I sell a product I have an
 
D

Digital2k

Thank you very much for your effort. I really appreciate your time. it was
close but it did not quite work foe me.
This formula was very close except it only works for the month of June:
=IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
else").
Click to expand...
Maybe you can modify it. Thank so much.
Digital2k
 
F

Franz Verga

I think this sould work:

=IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2))

Maybe it depends on how you consider the day that falls exactly on 3rd
Wednesday, maybe this is best suited to your needs:

=IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>3,B2="M"),MONTH(A2)+1,MONTH(A2))

Thank you very much for your effort. I really appreciate your time.
it was close but it did not quite work foe me.
This formula was very close except it only works for the month of
June:
=IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
Maybe you can modify it. Thank so much.
Digital2k
Click to expand...

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
D

daddylonglegs

Digital2k said:
How can I create a formula that will do this: = IF cell A2 ="m" and if
cell
D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m"
and D2
is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!
Click to expand...

Try this formula in E2

=IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")
 
B

Bobocat

hi, I setup an very long formula to calculate it, see whether is good for
you.
=IF(A2="m",IF(D2<=EOMONTH(D2,-1)+21-IF(WEEKDAY(EOMONTH(D2,-1)+19,2)>=3,(WEEKDAY(EOMONTH(D2,-1)+21,2)-2),(WEEKDAY(EOMONTH(D2,-1)+21,2)+5)),MONTH(D2),MONTH(D2)+1),"UNKNOWN")
 
D

Digital2k

You are the MAN!!!! This works Great! Thank you oh so very much!
There were a lot of people who were close, but you hit it on the head.
Thanks to all who tried, I really appreciate all tour efforts.
Digital2k

"daddylonglegs" <[email protected]>
wrote in message
 
D

Digital2k

Hello Group,

The formula given to me by daddylonglegs works fine but I want to add to the
equation:

=IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")I want to also add:IF(A2="N",D2,) Cell D2 has a date. So, if cell A2="N" I want to use thedate in D2 and change it to "mmm"in cell E2. And finally, I want to add thesame formula only using "O".When I try to add to the top formula, I get #VALUE!Please help with this"daddylonglegs" <[email protected]>wrote in messageDigital2k Wrote:>> How can I create a formula that will do this: = IF cell A2 ="m" and if>> cell>> D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m">> and D2>> is between 6/21/06 and 7/18/06 than E2 = "July" and so on?>> This is the formula I used:>>=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")>> I either get an #Value!, or some other error. Please help!>> Try this formula in E2>>=IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")>>> --> daddylonglegs> ------------------------------------------------------------------------> daddylonglegs's Profile:http://www.excelforum.com/member.php?action=getinfo&userid=30486> View this thread: http://www.excelforum.com/showthread.php?threadid=554930>
 
D

daddylonglegs

Not sure I completely understand. If A2="M" formula works as before but
if A2 = "N" or "O" you want D2's month (e.g. "June") in E2?

If so change formula to

=IF(OR(A2="N",A2="O"),TEXT(D2,"mmmm"),IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2
),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"" ))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date ranges 6
What formula can one use to Count dates? 8
Using VBA To Enter A Sumproduct Formula Into A Cell 0
Fomula for number of days on each month from a date range 3
if(isna(match formula 8
Please help with a date formula. 3
Difference between two date/time values 5
Problem with a small lookup database ..... ??? 1

Top