Solved

Months extract from formula

Posted on 2011-09-14
5
253 Views
Last Modified: 2012-05-12
Experts:

I need to modify this formula to return Months instead of the number of days.  
How can I do this?

=IF(GTExpireDate<FacilityExpirationDate,SUM(MID($D$15,SEARCH({"mon","day"},$D$15)-3,2)*{30,1}),"Expires After Facility Expiration")

ie it returns "60" for the number of days but I need it to return 2 months.

thank you
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 36536790
If you want to ignore the days and just take months try this version

=IF(GTExpireDate<FacilityExpirationDate,MID($D$15,SEARCH("mon",$D$15)-3,2)+0,"Expires After Facility Expiration")

regards, barry
0
 
LVL 10

Assisted Solution

by:Tony Barkdull
Tony Barkdull earned 250 total points
ID: 36536792
Just divide by 30...

=IF(GTExpireDate<FacilityExpirationDate,SUM(MID($D$15,SEARCH({"mon","day"},$D$15)-3,2)*{30,1}/30),"Expires After Facility Expiration")

0
 

Author Comment

by:pdvsa
ID: 36536901
it might be better to look at the file.

What I need is the remainder in months on row 36.  

should be 2.  

thank you
LC-Costs.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36537037
Did you try the formula I gave? That should give the result 2 - it just extracts the month figure from D15. What result would you want if the value in D15 was 4 years 2 months 20 days?

tbarkdull's suggestion will give you a fraction, e.g. 2.67, mine will still give you 2 - what should it be, either of those or something else?

regards, barry
0
 

Author Closing Comment

by:pdvsa
ID: 36538107
I am not sure which one at the moment.  Sorry got a little sidetracked with new job.  

thank you for the help.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

731 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question