Calculation Formula for dates in different format

[mark888]

Hi All,

Appreciate if anyone out there can help me on a formula on dates. I have an excel(attached) with 3 columns as below. Im trying to get Column C to calculate the days remaining from column B (B2-Now()) and if column D shows as Delivered then it should show as Done. Im having 3 problems as below:

Test File.xlsx

1) My formula in Column C cannot calculate more than 30 days.
2) Unable to map if Column C is Expired and Column D is Delivered then Column C should show as Done instead of ExpiredDone.
3) Is there anyway to calculate in Column C if column B shows value as Q2, 2016 instead of mmmm dd,yyyy

Yes it is very complicating to me and for the life of me I cannot think of another way to make this work. :-(

Kind Regards,
Mark.

[bebo021999]

My approach:
If status = Done, =>Done
If Date = Q1,2016, convert to first date of Q1,2016 = 01-Jan-2016 (similar to Q2: 01-Apr-2016 and so on)
If remaining date>30, displays "more than 30 days"

In C2:

Please Login or Register  to view this content.

[mark888]

My approach:
If status = Done, =>Done
If Date = Q1,2016, convert to first date of Q1,2016 = 01-Jan-2016 (similar to Q2: 01-Apr-2016 and so on)
If remaining date>30, displays "more than 30 days"

In C2:

Please Login or Register  to view this content.

Hello Bebo,

Nice to hear from you again. This works almost 99% for me. The remaining 1% would be if I could see those days ranging from 1 Day - 90 Days and the rest "More than 90 Days". Sadly 30 days does not work well for me. Anyway to have this? Tried changing your formula to >90 but does not seem to work. :-(

Kind Regards,
Mark.

[sktneer]

Not sure why Sucuri WebSite Firewall is not allowing me to post the formula here....
Please find the attached workbook to see if this is what you are trying to achieve.

[mark888]

Not sure why Sucuri WebSite Firewall is not allowing me to post the formula here....
Please find the attached workbook to see if this is what you are trying to achieve.

Hi Sktneer,

Yes this works like how i needed it. Only one thing though. Is it possible for the each Q1 to calculate as the last day of the Quarter instead of the first day?

Kind Regards,
Mark.

[sktneer]

Still unable to post the formula...
Tweaked the formula in a new column in the attached.

[JohnTopley]

@sktneer

Previous posts suggest the problem with Sucuri firewall could be ">" in a formula: apparently adding blanks as prefix/suffix gets round the problem

Assuming this is your formula:

=IF(D2="Delivered","Done",IF(B2 < TODAY(),"Expired",IF(LEFT(B2,1)="Q",DATE(RIGHT(B2,4),CHOOSE(MID(B2,2,1),1,4,7,10),1)-TODAY(),B2-TODAY())&" Days"))

[mark888]

Still unable to post the formula...
Tweaked the formula in a new column in the attached.

Hi sktneer,

Tried that formula for Q1, 2016 and it shows 13 Days. Must be some mistake there as it should show 73 Days instead as the end of Q1 is March 31st.

Kind Regards,
Mark.

[sktneer]

@John
Thanks for the information. I had no clue about that. lol

@Mark
Please replace the previous formula with the following one...

Please Login or Register  to view this content.

[mark888]

@John
Thanks for the information. I had no clue about that. lol

@Mark
Please replace the previous formula with the following one...

Please Login or Register  to view this content.

Hi sktneer,

Yes that works perfectly although when I do conditional formatting for cells less than 30 days to show it as Red, it appears other cells with value above 100+ days also becomes Red. Is it because the values are not in "dd" format?

Kind Regards,
Mark.

[sktneer]

Change your formula to the following one.....

Please Login or Register  to view this content.

Now select the formula cells --> Right click and choose Format Cells --> From the Number Tab select Custom and in the Type box, type the following format and click OK.

Please Login or Register  to view this content.

This custom formatting will add the text Days to the numbers in the formula cells. Now you may apply conditional formatting for the formula cells with Conditional Formatting --> Highlight Cells Rules --> Less than 30 and set the format as per your choice.

[mark888]

Change your formula to the following one.....

Please Login or Register  to view this content.

Now select the formula cells --> Right click and choose Format Cells --> From the Number Tab select Custom and in the Type box, type the following format and click OK.

Please Login or Register  to view this content.

This custom formatting will add the text Days to the numbers in the formula cells. Now you may apply conditional formatting for the formula cells with Conditional Formatting --> Highlight Cells Rules --> Less than 30 and set the format as per your choice.

Hi sktneer,

Yes, this works perfectly...Many thanks for your help. :-)

Kind Regards,
Mark

[mark888]

Change your formula to the following one.....

Please Login or Register  to view this content.

Now select the formula cells --> Right click and choose Format Cells --> From the Number Tab select Custom and in the Type box, type the following format and click OK.

Please Login or Register  to view this content.

This custom formatting will add the text Days to the numbers in the formula cells. Now you may apply conditional formatting for the formula cells with Conditional Formatting --> Highlight Cells Rules --> Less than 30 and set the format as per your choice.

Hi sktneer,

Yes, this works perfectly...Many thanks for your help. :-)

Kind Regards,
Mark

[sktneer]

You're welcome Mark! Glad I could help.
And thanks for the feedback as well.

[bebo021999]

@sktneer, your formula to define last day of quarter:

...DATE(RIGHT(B2,4),CHOOSE(MID(B2,2,1),3,6,9,12),IF(OR(MID(B2,2,1)="2",MID(B2,2,1)="3"),30,31))

could be simpler with another approach: define the 1st of next month then subtract 1

...DATE(RIGHT(B2,4),CHOOSE(MID(B2,2,1),4,7,10,13),1)-1

[sktneer]

Yes that is correct.