• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Excel Date Entered

eggyuk

Member
Vista Pro
Messages
135
Location
N. Yorks
#1
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

roy69

47,65,65,6B
Vista Guru
Gold Member
Messages
2,607
Location
uk
#2
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
    Mouse
    Technika TKOPTM2
    Keyboard
    Logik
    Internet Speed
    288 / 4000
    Other Info
    Creative Inspire 7.1 T7900 Speakers
    Trust Graphics Tablet

Dwarf

The Contemplator
Vista Guru
Gold Member
Messages
2,760
Location
Doncaster, UK
#3
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.

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...'.

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'.

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

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


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.

Secondly, Data 2 with tomorrow's date.

Thirdly, Data 3 with today's date.

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.

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.
 
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
    Mouse
    Microsoft Comfort Mouse 3000 for Business (USB)
    Keyboard
    Microsoft Comfort Curve Keyboard 3000 (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)

Dwarf

The Contemplator
Vista Guru
Gold Member
Messages
2,760
Location
Doncaster, UK
#4
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
    Mouse
    Microsoft Comfort Mouse 3000 for Business (USB)
    Keyboard
    Microsoft Comfort Curve Keyboard 3000 (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)

eggyuk

Member
Vista Pro
Messages
135
Location
N. Yorks
#5
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

eggyuk

Member
Vista Pro
Messages
135
Location
N. Yorks
#6
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

Dwarf

The Contemplator
Vista Guru
Gold Member
Messages
2,760
Location
Doncaster, UK
#7
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
    Mouse
    Microsoft Comfort Mouse 3000 for Business (USB)
    Keyboard
    Microsoft Comfort Curve Keyboard 3000 (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)

eggyuk

Member
Vista Pro
Messages
135
Location
N. Yorks
#8
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

Users Who Are Viewing This Thread (Users: 1, Guests: 0)