Excel Formula
Thread Starter
Joined: Dec 2014
Posts: 400
Likes: 13
From: North Yorkshire
Excel Formula
I need a cell to display the date (month+year e.g. 09/17) the adjacent cells in the same row were changed, if I change them. So for example if I change any cell between A and G in row 1, then cell H1 would automatically update to the current month of the year.
Hope that makes sense and MTIA for any forthcoming help...if it makes a difference I'm using Excel 2016.
Hope that makes sense and MTIA for any forthcoming help...if it makes a difference I'm using Excel 2016.
Joined: Dec 2013
Posts: 1
Likes: 0
From: Norfolk
The NOW() function will return the current date and time in a cell and of course you can call this function and enter it into a cell whenever data is changed, but the big problem with this approach is that it is not automatic and the date and time will change when the spreadsheet is recalculated unless you cut and copy the date and time and paste it back as text or some fixed value. Not a lot of help!
The solution lies with using a Visual Basic routine to insert this information for you and preserve the last date and time the data was changed. The exact details are far too complex to clearly lay out here, but a Google search for "Excel recording date and time of data change" will reveal several sites from which you may cut and paste the necessary code to suit your application.
The Visual Basic editor is part and parcel of Excel so you should have no problem following the instructions and getting one of these routines to work for your application.
The solution lies with using a Visual Basic routine to insert this information for you and preserve the last date and time the data was changed. The exact details are far too complex to clearly lay out here, but a Google search for "Excel recording date and time of data change" will reveal several sites from which you may cut and paste the necessary code to suit your application.
The Visual Basic editor is part and parcel of Excel so you should have no problem following the instructions and getting one of these routines to work for your application.
Joined: Mar 2003
Posts: 285
Likes: 0
From: England
Hi Co,
What you are asking for is somewhat non trivial. Because you need to keep track of the old data and when it is changes. Really, you should be using a database.
You can do it in Excel, but it is a bit like using a screwdriver as a chisel, it might work - but only with a lot more effort.
If I wanted something like this, I would be tempted to have a web page front end linked to a database all running on a raspberry pi.
EG
What you are asking for is somewhat non trivial. Because you need to keep track of the old data and when it is changes. Really, you should be using a database.
You can do it in Excel, but it is a bit like using a screwdriver as a chisel, it might work - but only with a lot more effort.
If I wanted something like this, I would be tempted to have a web page front end linked to a database all running on a raspberry pi.
EG
Joined: Jan 2008
Posts: 300
Likes: 0
From: London, England
Have a look at this...
https://www.extendoffice.com/documen...l-changes.html
https://www.extendoffice.com/documen...l-changes.html
