Formular to subtract a number per day

**dwarford101** · 08-05-2009, 06:07 AM

I needa formular that will subtract a number per day, for example as one day passes you can set the amount of number to be taken away from the total.
Can any one help or tell me if this is possible

**DonkeyOte** · 08-05-2009, 06:11 AM

You will need to provide more info... if for ex. you mean you want to do something along the lines of say:

subtract say 2 from the value specified in A1 for each day passed since the date specified in B1 up to today then

C1: =A1-2*(TODAY()-B1)

**dwarford101** · 08-05-2009, 06:16 AM

i have collected warrenty dates for my company and put the number of days left on the warrenty in cells, everyday that passes to take one off the number in the cells. So it keeps the numbers upto date.

**DonkeyOte** · 08-05-2009, 06:21 AM

Have you stored the warranty dates themselves ? You could then use a Formula to calculate days remaining based on current date. A sample file would help.

**dwarford101** · 08-05-2009, 06:25 AM

Originally Posted by DonkeyOte

Have you stored the warranty dates themselves ? You could then use a Formula to calculate days remaining based on current date. A sample file would help.

Not really, the website only displays the amount of dates left on the warrenty so only the amount of days are inserted. There are no dates left.
Thats why i though there may be a formular to subtract using the computers clock.

**DonkeyOte** · 08-05-2009, 06:41 AM

If there is no date to compare to the computer won't know as to when a further 1 was to be subtracted from the existing values unless the file runs 24x7.

Again, guessing having no file to work with, I would suggest you use a Workbook_Open event to compare the current date to a cell containing the last date the values were updated and if the difference > 1 day then reduce all values by the number of days having passed since last update, the date cell itself is then updated to reflect the fact that the current day calculation has been performed.

for sake of demo. let's assume you have numerical values in B1:B100 which are to be reduced by 1 each day, in C1 we have a static date stamp to indicate when the values were last adjusted, let's initially set to say 04-Aug-2009 (yesterday).

Private Sub Workbook_Open()
Dim vDays: vDays = Date - Sheets("Sheet1").Range("C1")
If Not IsNumeric(vDays) Then Exit Sub
Select Case vDays
    Case Is > 0
        Range("B1:B100").Value = Evaluate("IF(ROW(B1:B100)*ISNUMBER(B1:B100),B1:B100-" & vDays & ",B1:B100)")
        Range("C1").Value = Date
End Select
End Sub

The above would reside in ThisWorkbook Object in VBE.