EXCEL Vs. Time Left
March 25, 2011 9:37 AM Subscribe
Excel Question! I have two dates on my spreadsheet. How can I accurately determine (retrieve) the time remaining on a warranty date.
This has been a nightmare here at the office and none of us are Excel guru's.
We have a date of instal for something. Lets say the customer has 2 years until that service falls out of warranty. We need Excel to see the
=NOW() - for the current day of course, and the cell that has the date of service, say July 12th 2009. How can we show in the workbook how many months remain until the warranty is expired.
I have tried several variations using the DATEDIF function, but can only, obviously get different forms of the date differences. I need there to be a base of 2 years, 24 months whatever and subtract that from the current date =NOW() to give the customer the final months remaining based on the date of instal/service.
Please help us here, this has to be doable.
We are running Excel 2007
This has been a nightmare here at the office and none of us are Excel guru's.
We have a date of instal for something. Lets say the customer has 2 years until that service falls out of warranty. We need Excel to see the
=NOW() - for the current day of course, and the cell that has the date of service, say July 12th 2009. How can we show in the workbook how many months remain until the warranty is expired.
I have tried several variations using the DATEDIF function, but can only, obviously get different forms of the date differences. I need there to be a base of 2 years, 24 months whatever and subtract that from the current date =NOW() to give the customer the final months remaining based on the date of instal/service.
Please help us here, this has to be doable.
We are running Excel 2007
EDATE ( purchasedate, 24) will give you the final date of the warranty (based on 24 month warranty.
If you just take a date minus another date the result is the number of days between them. Excel basically treats dates as the number of days since day zero.
posted by mary8nne at 9:44 AM on March 25, 2011
If you just take a date minus another date the result is the number of days between them. Excel basically treats dates as the number of days since day zero.
posted by mary8nne at 9:44 AM on March 25, 2011
Response by poster: This is not working, typing it in exactly as displayed
=EDATE(D7, 24) - (G1)
I get (and this cant be right)
4/17/1900
posted by TeachTheDead at 9:48 AM on March 25, 2011
=EDATE(D7, 24) - (G1)
I get (and this cant be right)
4/17/1900
posted by TeachTheDead at 9:48 AM on March 25, 2011
Put the install date in cell B1
Put Warranty length (in days) in cell B2
Calculate the warranty end date (in cell B3) = B1 + B2
Calculate days left (in cell B4) = B3 - NOW()
posted by jonesor at 9:52 AM on March 25, 2011
Put Warranty length (in days) in cell B2
Calculate the warranty end date (in cell B3) = B1 + B2
Calculate days left (in cell B4) = B3 - NOW()
posted by jonesor at 9:52 AM on March 25, 2011
Best answer: Assuming your start date is in cell A1:
=24-DATEDIF(A1,now(),"m")
This calculates the number of months (indicated by the "m") between now and the start date and subtracts that from your warranty term of 24 months, giving you the remainder of months on the warranty. If you get a result that looks like a date, just reformat the cell as number format and you should be good to go.
posted by stefanie at 9:58 AM on March 25, 2011 [2 favorites]
=24-DATEDIF(A1,now(),"m")
This calculates the number of months (indicated by the "m") between now and the start date and subtracts that from your warranty term of 24 months, giving you the remainder of months on the warranty. If you get a result that looks like a date, just reformat the cell as number format and you should be good to go.
posted by stefanie at 9:58 AM on March 25, 2011 [2 favorites]
Format Problem: my suggestion is working perfectly its just that you have the cell contents formatted as a date when its a number - reformat the 4/17/1900 as a NUMBER or $ or anything other than a date.
posted by mary8nne at 10:10 AM on March 25, 2011
posted by mary8nne at 10:10 AM on March 25, 2011
Response by poster: jonesor - so close, the Days left returns a bunch of ####'s
posted by TeachTheDead at 10:14 AM on March 25, 2011
posted by TeachTheDead at 10:14 AM on March 25, 2011
thats just a formatting issue as well. you need to increase the column width
posted by mary8nne at 10:18 AM on March 25, 2011
posted by mary8nne at 10:18 AM on March 25, 2011
Response by poster: stefanie, perfect, Thank you so much, hate to waste my question on this, but we just could not figure this one out. As always, you guys rock.....hard.
posted by TeachTheDead at 10:20 AM on March 25, 2011
posted by TeachTheDead at 10:20 AM on March 25, 2011
This thread is closed to new comments.
posted by mary8nne at 9:42 AM on March 25, 2011