Calculate difference between two dates

921 Views 4 Replies 2 Participants Last post by cwwozniak, Jan 30, 2016

FernJones

Discussion Starter · Jan 29, 2016

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.

Save

Status: Not open for further replies.

1 - 5 of 5 Posts

cwwozniak

· #2 · Jan 29, 2016

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.

Save

cwwozniak

· #3 · Jan 29, 2016

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

Save

FernJones

Discussion Starter · #4 · Jan 30, 2016

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.

Save

Reactions: 1