Excel Date Entered

eggyuk

Member
Vista Pro
I have a spreadsheet in excel that contains columns such as Name, DOB, form no etc, and I want to add a column that will auto enter the date that the information was entered. I tried adding a column which uses the TODAY() function when something is added to the column to the left but everyday it updates the TODAY() to current day so that won't work. Can anybody think of a way to stop it updating or another way to put the "entered date" on?
 

My Computer

System One

  • CPU
    AMD Athlon 64 X2 4800
    Motherboard
    ASUS M2N-SLI Deluxe
    Memory
    4GB OCZ PC-6400
    Graphics Card(s)
    Sapphire HD4850 Toxic Edition
    Monitor(s) Displays
    Samsung SM2493HW
    Hard Drives
    500GB Seagate, 160GB Seagate
    PSU
    Corsair HX520W
    Case
    Antec P182 Gunmetal Grey
try the now() command, this will not update automatically.

This is the help text from excel

Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.
Syntax
NOW( )



Remarks
  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
  • Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number .5 represents the time 12:00 noon.
  • The NOW function changes only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
 

My Computer

System One

  • Manufacturer/Model
    Self Built
    CPU
    I5 3570K
    Motherboard
    Gigabyte Z77-DS3H
    Memory
    4 x 4GB corsair ballistix sport DDR3 1600 Mhz
    Graphics Card(s)
    Gigabyte Geforce GTX 660 TI
    Sound Card
    creative x-fi
    Monitor(s) Displays
    Primary CiBox 22" Widescreen LCD ,Secondary Dell 22" Widescreen
    Screen Resolution
    Both 1680 x 1050
    Hard Drives
    2 x 500G HD (SATA) 1 x 2TB USB
    PSU
    Corsair HX 620W ATX2.2 Modular SLI Complient PSU
    Case
    Antec 900 Ultimate Gaming Case
    Cooling
    3 x 80mm tri led front, 120mm side 120mm back, 200mm top
    Keyboard
    Logik
    Mouse
    Technika TKOPTM2
    Internet Speed
    288 / 4000
    Other Info
    Creative Inspire 7.1 T7900 Speakers
    Trust Graphics Tablet
Hi eggyuk,

I have managed to find a solution for you. This is using Office 2007. The first image shows the headings I have used for my example.
attachment.php

The next 2 images show how to format columns A and B to show dates. Right click on the column heading and select 'Format Cells...'.
attachment.php

In the 'Number' tab, under 'Category:' select 'Date'. In the panel under 'Type:', select how you want the date to appear and then click 'OK'
Capture2.JPG
The formula used in column A refers back to itself, so these next 2 images show how to enable this. Click on the Office button and then click 'Excel Options'.
attachment.php

Select the 'Formulas' page and then click to put a tick in the checkbox 'Enable iterative calculation' and then click 'OK'.
attachment.php

The next 2 images show the formulas for columns A and B respectively.
attachment.php

attachment.php

The next sequence of images shows this in action. The data I used in all cases was simply the word 'test', but of course you can change this to what you like.
Firstly, Data 1 with today's date.
attachment.php

Secondly, Data 2 with tomorrow's date.
attachment.php

Thirdly, Data 3 with today's date.
attachment.php

Note how in each case column A remains constant whilst column B changes to reflect the current date.
Finally, deleting all data from columns C, D, and E. Notice how columns A and B clear as well.
attachment.php

Hope this helps. At the moment, there is still a bug in the formula for column B in that it still changes for all rows if you create, delete, edit or modify any of the data in any row. I am looking at this so that only the current row that you are working on is affected. When I have found the solution, I shall post it here.
 

Attachments

  • Capture.JPG
    Capture.JPG
    58.4 KB · Views: 2,733
  • Capture1.JPG
    Capture1.JPG
    65.7 KB · Views: 2,758
  • Capture3.JPG
    Capture3.JPG
    52.7 KB · Views: 2,741
  • Capture4.JPG
    Capture4.JPG
    75.6 KB · Views: 2,725
  • Capture5.JPG
    Capture5.JPG
    79.6 KB · Views: 2,646
  • Capture6.JPG
    Capture6.JPG
    61.2 KB · Views: 2,638
  • Capture7.JPG
    Capture7.JPG
    60.4 KB · Views: 2,649
  • Capture8.JPG
    Capture8.JPG
    60.3 KB · Views: 2,635
  • Capture9.JPG
    Capture9.JPG
    61.3 KB · Views: 2,622
  • Capture10.JPG
    Capture10.JPG
    57.7 KB · Views: 2,614
Last edited:

My Computer

System One

  • Manufacturer/Model
    Dwarf Dwf/11/2012 r09/2013
    CPU
    Intel Core-i5-3570K 4-core @ 3.4GHz (Ivy Bridge) (OC 4.2GHz)
    Motherboard
    ASRock Z77 Extreme4-M
    Memory
    4 x 4GB DDR3-1600 Corsair Vengeance CMZ8GX3M2A1600C9B (16GB)
    Graphics Card(s)
    MSI GeForce GTX770 Gaming OC 2GB
    Sound Card
    Realtek High Definition on board solution (ALC 898)
    Monitor(s) Displays
    ViewSonic VA1912w Widescreen
    Screen Resolution
    1440x900
    Hard Drives
    OCZ Agility 3 120GB SATA III x2 (RAID 0)
    Samsung HD501LJ 500GB SATA II x2
    Hitachi HDS721010CLA332 1TB SATA II
    Iomega 1.5TB Ext USB 2.0
    WD 2.0TB Ext USB 3.0
    PSU
    XFX Pro Series 850W Semi-Modular
    Case
    Gigabyte IF233
    Cooling
    1 x 120mm Front Inlet 1 x 120mm Rear Exhaust
    Keyboard
    Microsoft Comfort Curve Keyboard 3000 (USB)
    Mouse
    Microsoft Comfort Mouse 3000 for Business (USB)
    Internet Speed
    NetGear DG834Gv3 ADSL Modem/Router (Ethernet) ~4.0 Mb/s (O2)
    Other Info
    Optical Drive: HL-DT-ST BD-RE BH10LS30 SATA Bluray
    Lexmark S305 Printer/Scanner/Copier (USB)
    WEI Score: 8.1/8.1/8.5/8.5/8.25
    Asus Eee PC 1011PX Netbook (Windows 7 x86 Starter)
Hi eggyuk,

Unfortunately, I have not been able to come up with a solution for this bug with the formula for column B in my example (perhaps the developers of Excel will come up with a function that can detect which row and column the cursor is on, which seems to be the sticking point).
I therefore propose that you delete column B in my example and, instead, modify the formula in Column A to read as follows (increase or decrease the number of terms in the first nested () to take account of the number of columns you are dealing with):
Code:
=IF(OR(B2<>"",C2<>"",D2<>""),IF(A2<>"",A2,TODAY()),"")
Remember that column A will only show the date that the FIRST bit of information was entered in any of the associated columns, and that it will stay showing that date even if you subsequently amend any of the data on THAT row irrespective of the current date. As before, if you delete all the data in the row then column A will automatically clear on that row.
 

My Computer

System One

  • Manufacturer/Model
    Dwarf Dwf/11/2012 r09/2013
    CPU
    Intel Core-i5-3570K 4-core @ 3.4GHz (Ivy Bridge) (OC 4.2GHz)
    Motherboard
    ASRock Z77 Extreme4-M
    Memory
    4 x 4GB DDR3-1600 Corsair Vengeance CMZ8GX3M2A1600C9B (16GB)
    Graphics Card(s)
    MSI GeForce GTX770 Gaming OC 2GB
    Sound Card
    Realtek High Definition on board solution (ALC 898)
    Monitor(s) Displays
    ViewSonic VA1912w Widescreen
    Screen Resolution
    1440x900
    Hard Drives
    OCZ Agility 3 120GB SATA III x2 (RAID 0)
    Samsung HD501LJ 500GB SATA II x2
    Hitachi HDS721010CLA332 1TB SATA II
    Iomega 1.5TB Ext USB 2.0
    WD 2.0TB Ext USB 3.0
    PSU
    XFX Pro Series 850W Semi-Modular
    Case
    Gigabyte IF233
    Cooling
    1 x 120mm Front Inlet 1 x 120mm Rear Exhaust
    Keyboard
    Microsoft Comfort Curve Keyboard 3000 (USB)
    Mouse
    Microsoft Comfort Mouse 3000 for Business (USB)
    Internet Speed
    NetGear DG834Gv3 ADSL Modem/Router (Ethernet) ~4.0 Mb/s (O2)
    Other Info
    Optical Drive: HL-DT-ST BD-RE BH10LS30 SATA Bluray
    Lexmark S305 Printer/Scanner/Copier (USB)
    WEI Score: 8.1/8.1/8.5/8.5/8.25
    Asus Eee PC 1011PX Netbook (Windows 7 x86 Starter)
hi guys. Thanks for your replies. Unfortunately I've been majorly busy at work recently so I haven't been able to try them out yet. As soon as I can make time to "play" I'll let u know how it went. Thanks again
 

My Computer

System One

  • CPU
    AMD Athlon 64 X2 4800
    Motherboard
    ASUS M2N-SLI Deluxe
    Memory
    4GB OCZ PC-6400
    Graphics Card(s)
    Sapphire HD4850 Toxic Edition
    Monitor(s) Displays
    Samsung SM2493HW
    Hard Drives
    500GB Seagate, 160GB Seagate
    PSU
    Corsair HX520W
    Case
    Antec P182 Gunmetal Grey
Ok I've had a play around and it works on my spreadsheet so far. It is correctly entering todays date when i put something in another cell. I will check that it doesnt change the date when i enter somethnig tomorrow and report back.
 

My Computer

System One

  • CPU
    AMD Athlon 64 X2 4800
    Motherboard
    ASUS M2N-SLI Deluxe
    Memory
    4GB OCZ PC-6400
    Graphics Card(s)
    Sapphire HD4850 Toxic Edition
    Monitor(s) Displays
    Samsung SM2493HW
    Hard Drives
    500GB Seagate, 160GB Seagate
    PSU
    Corsair HX520W
    Case
    Antec P182 Gunmetal Grey
Hi eggyuk,

To check this out, temporarily change the formula in column A as follows:
Code:
=IF(OR(B2<>"",C2<>"",D2<>""),IF(A2<>"",A2,NOW()),"")
In addition, change the cell format on column A from the date format to 'General'. In the rightmost column (the first column that you don't use), ensure that the format is set to 'General' and simply enter the formula:
Code:
=NOW()
When you amend any of your data, you will find that the value in column A will stay constant, but that values in this column will change.
Once you are satisfied with the functioning of this, you can revert column A back to its original state (formula and column format) and delete the extra column.
Incidentally, I used the 'NOW()' function as opposed to the 'TODAY()' function when I was originally working out my original solution as it was much easier. After I had tested it and confirmed that it was working, I then substituted 'TODAY()' into the formula in place of 'NOW()'. I also changed the format of the column so that the information would be displayed correctly.
 

My Computer

System One

  • Manufacturer/Model
    Dwarf Dwf/11/2012 r09/2013
    CPU
    Intel Core-i5-3570K 4-core @ 3.4GHz (Ivy Bridge) (OC 4.2GHz)
    Motherboard
    ASRock Z77 Extreme4-M
    Memory
    4 x 4GB DDR3-1600 Corsair Vengeance CMZ8GX3M2A1600C9B (16GB)
    Graphics Card(s)
    MSI GeForce GTX770 Gaming OC 2GB
    Sound Card
    Realtek High Definition on board solution (ALC 898)
    Monitor(s) Displays
    ViewSonic VA1912w Widescreen
    Screen Resolution
    1440x900
    Hard Drives
    OCZ Agility 3 120GB SATA III x2 (RAID 0)
    Samsung HD501LJ 500GB SATA II x2
    Hitachi HDS721010CLA332 1TB SATA II
    Iomega 1.5TB Ext USB 2.0
    WD 2.0TB Ext USB 3.0
    PSU
    XFX Pro Series 850W Semi-Modular
    Case
    Gigabyte IF233
    Cooling
    1 x 120mm Front Inlet 1 x 120mm Rear Exhaust
    Keyboard
    Microsoft Comfort Curve Keyboard 3000 (USB)
    Mouse
    Microsoft Comfort Mouse 3000 for Business (USB)
    Internet Speed
    NetGear DG834Gv3 ADSL Modem/Router (Ethernet) ~4.0 Mb/s (O2)
    Other Info
    Optical Drive: HL-DT-ST BD-RE BH10LS30 SATA Bluray
    Lexmark S305 Printer/Scanner/Copier (USB)
    WEI Score: 8.1/8.1/8.5/8.5/8.25
    Asus Eee PC 1011PX Netbook (Windows 7 x86 Starter)
OK. I've managed to test it out and it works perfectly. Hopefully I'll get a promotion/pay rise as a result of my new improved spreadsheet that solved a problem that didnt exist and I just made it cos I was bored at work one day. Somehow I dont think i will get that raise :huh:

Thanks for the help Dwarf. Greatly appreciated.
 

My Computer

System One

  • CPU
    AMD Athlon 64 X2 4800
    Motherboard
    ASUS M2N-SLI Deluxe
    Memory
    4GB OCZ PC-6400
    Graphics Card(s)
    Sapphire HD4850 Toxic Edition
    Monitor(s) Displays
    Samsung SM2493HW
    Hard Drives
    500GB Seagate, 160GB Seagate
    PSU
    Corsair HX520W
    Case
    Antec P182 Gunmetal Grey
Back
Top