Connecting a date with a number in excel

ctjacobs2010

Board Regular
Joined
Dec 12, 2013
Messages
52
I am new here and I am trying to create an if-then function that connects a date to a number. So for example if I had the date 19 Years, 3 Months, 0 Days in cell A1, I want to create an if-then function in B1 that says
If cell A1 is less than 5 years then 3.69. If A1 is equal to 5 up to anything less than 10 than 5.23. If A1 is equal to 10 and less than 15 than 5.85. If A1 is equal to 15 and anything above that is 6.77

I thought it would work if I did this:
=if(A1<5,3.69, if(A1=5-<10,5.23, if(A1=10-<15,5.58, if(A1=15+,6.77,0)

but this didn’t work and I think it is because 5, 10,and 15 are referring to numbers not years and I need it to reference the year in A1

Any help would be greatly appreciated

Thank You,
Ctjacobs2010
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,396
Office Version
  1. 365
Platform
  1. MacOS
what format is the date entered
if not a number , is it a date format
and if so how is
19 Years, 3 Months, 0 Days
shown in the cell - not sure how you would show a day of zero as a date ?


 

ctjacobs2010

Board Regular
Joined
Dec 12, 2013
Messages
52
I usede this formula to get that number shown above

=DATEDIF(E7,TODAY(),"Y") & " Years, " & DATEDIF(E7,TODAY(),"YM") & " Months, " & DATEDIF(E7,TODAY(),"MD") & " Days"

It is a general format not a date format
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,396
Office Version
  1. 365
Platform
  1. MacOS
so you are producing text and not values
not clear on the IF statement what you are testing

then you could use datedif in the if statement
 

ctjacobs2010

Board Regular
Joined
Dec 12, 2013
Messages
52

ADVERTISEMENT

is there any way I can private message you so I can help you understand a little better. I really need to get this figured out and it is complicated to explain. It is probably something really simple to figure out I just cant seemed to do so
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,396
Office Version
  1. 365
Platform
  1. MacOS
You would be better off - trying to post the info in a reply, as that will allow all the members here to be able to reply, and although I can answer some questions i'm not an expert, and other members have far more experience then I and so by explaining here - you have more chance of a solution

also you could use dropbox/skydrive or other sharing site to post an example
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,396
Office Version
  1. 365
Platform
  1. MacOS
note datedif
will produce 0 for upto 1 year
1 for upto 2 years

so I have used <4 , <9, <14

if this is not correct let me know what the issue , perhaps with some example dates and because of timezones - what today() equals

=IF(DATEDIF(E2,TODAY(),"y") < 4, 3.69,IF(DATEDIF(E2,TODAY(),"y") < 9, 5.23,IF(DATEDIF(E2,TODAY(),"y") < 14, 5.85, 6.77)))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,551
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top