Can you help me with formula formatting in a spreadsheet?

Lisa Towne March 4, 2013
Pinterest Stumbleupon Whatsapp

I have a spread sheet in which I track approximately 165 boxes that go out to customers daily.

I track them by account number and then customer name. I would now like to try to come up with a formula that would automatically set the date when I change the account number in B3 and the account name in C3 on the same day. I would then like to show in another cell the number of days a box has sat at the account.

I think I have the correct formula for tracking how many days =DATEDIF(E3,F3,”G”). (cell E3 should be equal to the day changed, F3 is =TODAY()) which should give me the days it has sat, but I am completely at a loss on how to formulate cell E3 to show the date I changed cells B3 & C3.
See example below.

BOX3 ACCT# ACCOUNT NAME DAY CHANGED TODAY’S DATE BOX DAYS
1 EXMP1 EXAMPLE 1 03/04/2013 #VALUE!
2 EXMP2 EXAMPLE 2
3 EXMP3 EXAMPLE 3
4 EXMP4 EXAMPLE 4

Thank you!

Ads by Google

  1. Alan Wade
    March 5, 2013 at 7:11 am

    A site that offers excellant Excel help is this one: http://www.excelforum.com/ You will need to join but it is free and the help they offer is really good.

  2. Bruce Epper
    March 5, 2013 at 2:24 am

    Are we to assume that column F has the formula =TODAY() for every row in the spreadsheet? If so, this is an unnecessary use of a column since this data does not vary for any record at all. In that case, the entire column can be deleted and the column where you want the number of days for the boxes is should be =TODAY()-E3. This will tell you how long is has been since the other date column has been changed for the record in row 3.

    If this is a record of shipments, why are you overwriting data? Will you never have a need to access historical data or are you using the same spreadsheet as a template and writing a new copy for each day? Have you considered using a database for this instead. It is more efficient and can automatically inject the current date for each new record.

    I'll look into what can be done regarding automatically changing the date in Excel, but it isn't something a formula can solve; it will require a macro at least or a custom DLL to extend the functionality of Excel itself.

    • Lisa Towne
      March 5, 2013 at 2:14 pm

      Thank you Bruce. I have tracked changes in the past but this is something new that I have been asked to add to my spread sheet. I have made a copy of the original spread sheet and will keep my original to keep track of my changes prior to adding the new tracking to it. I wasn't sure if I could do it with a formula or if I would need a macro and no matter what I did try it didn't work. For now I will enter by hand the days that I change information but it would be much easier if there was a way for the spread sheet to track it for me. I appreciate your assistance with this.