Need quick formula for Microsoft Excel
Budget: $15 – $25 USD
I'm using both Word and Excel to create a Microsoft Word Mail Merge.
The goal is to take today's date (it'd be ideal if it would self-update in the formula, if not it would be ok if I just need to change it each time I do a mail merge), subtract 10 years from it, add "Original Date" or [B1], and produce a result in the formula field formatted in years, months, days, showing how many days until expiration. The formula field would show how many days from today until expiration. Items expire 10 years after original date.
I need an Excel formula, but it has to be tested such that when I merge that field into a Word doc, it will display as I want it to.
One of the Excel fields is the original date of an item. For the formula, we'll call it "Original Date", or if I can just use the excel field the date is in, [B1] or whatever the field is.
To simplify, it'd be ok to use 30 day months and 360 day years, though if possible I'd rather use the correct number of days in each month.
Example: "Original Date" or [B1] = 2/14/2012
Today is 12/4/2022. Minus 10 years = 12/4/2012. Add [B1], or an answer of 2/14/12 (which is equal to 2 months, 8 days , 62 days, using the correct numbers of 31 days in December, 30 in January) (or rounding to 30 days months if necessary, it would be the 2 months, 7 days, or 61 days because the 31st of January would not be counted).
I probably made that sound way more complicated than it is. I'll try to summarize with an example:
My original date field in excel is [B1] (actually column B).
Today's date is D.
(D - 10 years) + B1 is the result I need. I'd prefer that result use the actual number of days in each month, which I guess could only be accomplished if Excel lets you perform the function of subtracting one date from another. Otherwise, use 30 days in a month, 360 days in a year.
Use exactly 10 years for the 10 year function, so 3650 if it has to be converted to days.
To re-iterate: I need an Excel formula that will take one date field, subtract 10 years or 3650 days, add another date field, and produce a result ideally formatted in Years, months, days (ie: 1 year, 3 months, 2 days).
Another way to achieve this would be to take the original date [B1], add 10 year or 3650 days, and subtract the result from today's date. Either syntax should give the same result.
I'll pay the first person to send me a message with a working formula. So if you see a bunch of other people have already sent me messages, I probably already have the answer but just have not received and tried it yet.
Thanks in advance!!
Lou
The goal is to take today's date (it'd be ideal if it would self-update in the formula, if not it would be ok if I just need to change it each time I do a mail merge), subtract 10 years from it, add "Original Date" or [B1], and produce a result in the formula field formatted in years, months, days, showing how many days until expiration. The formula field would show how many days from today until expiration. Items expire 10 years after original date.
I need an Excel formula, but it has to be tested such that when I merge that field into a Word doc, it will display as I want it to.
One of the Excel fields is the original date of an item. For the formula, we'll call it "Original Date", or if I can just use the excel field the date is in, [B1] or whatever the field is.
To simplify, it'd be ok to use 30 day months and 360 day years, though if possible I'd rather use the correct number of days in each month.
Example: "Original Date" or [B1] = 2/14/2012
Today is 12/4/2022. Minus 10 years = 12/4/2012. Add [B1], or an answer of 2/14/12 (which is equal to 2 months, 8 days , 62 days, using the correct numbers of 31 days in December, 30 in January) (or rounding to 30 days months if necessary, it would be the 2 months, 7 days, or 61 days because the 31st of January would not be counted).
I probably made that sound way more complicated than it is. I'll try to summarize with an example:
My original date field in excel is [B1] (actually column B).
Today's date is D.
(D - 10 years) + B1 is the result I need. I'd prefer that result use the actual number of days in each month, which I guess could only be accomplished if Excel lets you perform the function of subtracting one date from another. Otherwise, use 30 days in a month, 360 days in a year.
Use exactly 10 years for the 10 year function, so 3650 if it has to be converted to days.
To re-iterate: I need an Excel formula that will take one date field, subtract 10 years or 3650 days, add another date field, and produce a result ideally formatted in Years, months, days (ie: 1 year, 3 months, 2 days).
Another way to achieve this would be to take the original date [B1], add 10 year or 3650 days, and subtract the result from today's date. Either syntax should give the same result.
I'll pay the first person to send me a message with a working formula. So if you see a bunch of other people have already sent me messages, I probably already have the answer but just have not received and tried it yet.
Thanks in advance!!
Lou