Need some help in Excel. $5 Paypal reward for the help

[TangoJuliet]

If any of you visited MacRumors Buyers Guide you will not they have a notation of when a product was last update (ie 400 days). I would like to do the same thing in excel.

I have a column that shows something was updated on 5/5/10. I would like it to continually update to show how many days its been since it was updated. I'm not even sure if there is a formula that can do this but I come to AT for the help.

Thanks!!

 

[Tweak155]

Need a better description or maybe a screen shot with arrows pointing out what needs to happen. Is this supposed to read from a website?

 

[BrokenVisage]

Actually.. maybe this could help? I wanted a function to show how long it's been since a help ticket was acknowledged, so I use the acknowledgement dates in one column formatted as mm/dd/yyyy and use this function to convert it to a julian date along with today's date and subtract one from the other to get the number of days between the 2.

=SUM((TEXT(TODAY(),"yy")&TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000"))-(TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")))

So, say your "updated" column with dates started in A1 (if not, change all the A1's in my string to what you need) all you have to do is paste the function into a cell on the same row and drag down as far as you need, the numbers will be how many days are between today and that date on each row. Save it, then each time you open the workbook the number should change dynamically depending on what today's date is. Not sure if it's what you need but maybe it helps.

 

[JoeyP]

From your description, it sounds like you need some sort of record of a cell change having happened. If your column that you want to track is column 3 (C), you can record the date/time of change in column 4, and put the days since that change in column 5.

Open VBA editor, and add this to a Module:

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        MsgBox ("saw cell in col 3 change")
        ' Put today's date in col 4
        Cells(Target.Row, 4).Value = Now
    End If
End Sub

Then put this formula in column 5 (assuming you're in row 15) would be:
=IF(OR(C15="",D15=""),"",ROUND(NOW()-D15,0))

Drag that up and down to calculate the cells.

 

[zzuupp]

If the date is cell A1,
=int(now()-A1)

 

[quakeworld]

Hit F11 when in Excel

Then insert/module

then copy and paste this:

Sub Workbook_Open

MsgBox (Int(Now - Range("a2").Value))

end sub

It'll pop up a messagebox that tells you the number of days since the last update everytime the workbook opens.

 

[Snapster]

If the date is cell A1,
=int(now()-A1)

If I could, I'd upvote this as the answer, along with suggesting to use fill down (ctrl+d) to save some time.

 

[JoeyP]

Is the date of change recorded somewhere in the row? If so, then post #5 has a good solution.

If the row doesn't have this date, you'll want to trigger off some other change (like a price in a certain column).

 

[TangoJuliet]

Sorry it took so long to respond I was busy doing something

Here is a screen shot of what I am doing. Date Submitted you dont have to worry about. Date modified is when the last person did something to it. The "today" column will be so that we can see how long it has been since it was modified.

ie. In row 2 it says item 11343 was submitted on 1/27/09, modified on 6/3/11 and hasn't been "touched" in 400 (or so) days....the amount of time from 6/3/11 to today 9/14/12

Here is a screen shot

 

[postmark]

Sorry it took so long to respond I was busy doing something

Here is a screen shot of what I am doing. Date Submitted you dont have to worry about. Date modified is when the last person did something to it. The "today" column will be so that we can see how long it has been since it was modified.

ie. In row 2 it says item 11343 was submitted on 1/27/09, modified on 6/3/11 and hasn't been "touched" in 400 (or so) days....the amount of time from 6/3/11 to today 9/14/12

Here is a screen shot

Just like people said above. In column G just put this function and fill down:

=INT(NOW()-F1)

That will make the today column = number of days since last modified.

 

[Charles Kozierok]

Just like people said above. In column G just put this function and fill down:

=INT(NOW()-F1)

That will make the today column = number of days since last modified.

Yep.

Or if you want to get fancy, something like:

=IF(INT(NOW()-F1)=0,"Today",IF(INT(NOW()-F1)=1,"Yesterday",INT(NOW()-F1)&" Days Ago"))

 

[TangoJuliet]

Can't seem to get it to work. Can I send you the file and you edit it for me?

 

[BrokenVisage]

lol @ DELETE ME!!.xlsx

 

[Charles Kozierok]

Can't seem to get it to work. Can I send you the file and you edit it for me?

Sure, fire away. I do this stuff all the time.

 

[TangoJuliet]

Sure, fire away. I do this stuff all the time.

Thanks, can u PM me your email address? Also, is it possible to do a checkbox with a date stamp?