SolvedCalculate difference between two dates

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

FernJones

Fern
Hello,

I'm trying to save time by creating a formula for something I do every week, and I'm stuck. I wonder if someone would be able to help me out.

I have two columns, they each contain a date. I need a formula to subtract Col A from Col B to calculate the difference, then to assign a category to them:

Col A Col B Difference Time Group
12/1/2015 7/1/2016 213 6-9 months

My time groups:
Less than 90 W/n 3 mos
91-180 3-6 mos
181-270 6-9 mos
271-360 9-12 mos
361-450 12-15 mos
451-540 15-18 mos
541+ 18+ mos

Thank you very much in advance for any help.
Fern.

cwwozniak

Chuck
Moderator
It would help if you told us what software you are working with.

If you are using some version of Microsoft Excel, you should format columns A and B to use your desired date format. Subtracting their values will return the number of days. Using Excel's VLOOKUP function is probably the easiest way to convert the number of days to the desired group description.

I have attached an example MS-Excel file.

cwwozniak

Chuck
Moderator
Just noticed I left out one date range. Revised file attached

Attachments

• 32 KB Views: 7

FernJones

Fern
Hi.
Sorry, I did forget to mention that it's Excel I'm using.
And thank you so much for the VLOOKUP idea, I didn't even think about that. Your excellent suggestion and will save me so much time every week. I really appreciate your help!
Thanks and have a great weekend!
Fern.

cwwozniak

Chuck
Moderator
You're welcome, Fern.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On