• Status: Solved
• Priority: Medium
• Security: Public
• Views: 268

# Months extract from formula

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
pdvsa
• 2
• 2
2 Solutions

Commented:
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

Commented:
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

Project financeAuthor Commented:
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

Commented:
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

Project financeAuthor Commented:
I am not sure which one at the moment.  Sorry got a little sidetracked with new job.

thank you for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.