Andreamary
asked on
Keep time stamp but have it hidden in column view in Excel spreadsheet
Our spreadsheet uses a lookup table, which is based on the following formulas that concatenate the staff's initials + date/time stamp:
="KB–"&TEXT(NOW(),"dd mmm yy–hh:mm")
="AC–"&TEXT(NOW(),"dd mmm yy–hh:mm")
="MN–"&TEXT(NOW(),"dd mmm yy–hh:mm")
="MC–"&TEXT(NOW(),"dd mmm yy–hh:mm")
We have purposely included 'hh:mm' in the formulas above, but want the time stamp only show in the formula bar when the cell is highlighted, and NOT in the column within the spreadsheet (makes column too wide in an already very large spreadsheet). For the column in the spreadsheet, we want just the initials & date to show. Is this possible?
I've included a sample spreadsheet.
Thanks,
Andrea
Initials-Date-Time-Stamp.xlsx
="KB–"&TEXT(NOW(),"dd mmm yy–hh:mm")
="AC–"&TEXT(NOW(),"dd mmm yy–hh:mm")
="MN–"&TEXT(NOW(),"dd mmm yy–hh:mm")
="MC–"&TEXT(NOW(),"dd mmm yy–hh:mm")
We have purposely included 'hh:mm' in the formulas above, but want the time stamp only show in the formula bar when the cell is highlighted, and NOT in the column within the spreadsheet (makes column too wide in an already very large spreadsheet). For the column in the spreadsheet, we want just the initials & date to show. Is this possible?
I've included a sample spreadsheet.
Thanks,
Andrea
Initials-Date-Time-Stamp.xlsx
That formula will be volatile, i.e it will change. so it will not be a time stamp. To create a non volatile time stamp you would need a VBA function
ASKER
Hi Roy,
In response to your note I went back to reopen my sample spreadsheet, and the entries in Column B have maintained the original date and time stamp and did not update. Any new entries, of course, will reflect the current date and time, which is the behaviour I want. Perhaps I'm not understanding what you are referring to as being volatile?
Thanks,
Andrea
In response to your note I went back to reopen my sample spreadsheet, and the entries in Column B have maintained the original date and time stamp and did not update. Any new entries, of course, will reflect the current date and time, which is the behaviour I want. Perhaps I'm not understanding what you are referring to as being volatile?
Thanks,
Andrea
The time stamp update when the sheet calculates. Try hitting Calculate Now in the formula tab
ASKER
I hit the 'Calculate Now' and the original date/time stamps held and did not update. Is my sample spreadsheet behaving differently at your end?
Andrea
Andrea
Why not set the width of the column such that it just shows the part of the value that you want to see?
ASKER
Several of my macros resize columns automatically to show all the data. To avoid this particular column from auto resizing, how do I set the column width so that it defaults to the size I want? BTW, the width I would set it at would be 14.
Thanks!
Andrea
Thanks!
Andrea
At the end of those macros add
Columns("B:B").Select
Selection.ColumnWidth = 14
Columns("B:B").Select
Selection.ColumnWidth = 14
Or better would be to just change the macro so the resizing doesn't resize column B in the first place. And if you need help doing that then please post the code for the macro.
ASKER
Thanks, Martin. On closer inspection, it appears my macro doesn't auto resize the columns, it just unhides all columns. If I could incorporate your suggested code into this existing macro, that would be great. Here is the current code:
Thanks!
Andrea
Sub UnhideALLColumn()
'
' UnhideALLColumn Macro
'
'
Columns("A:FB").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
End Sub
Thanks!
Andrea
The Timestamps update on the downloaded example.
ASKER
Hi Roy,
Just to confirm that in Column A, the lookup column, the time stamps update (which we want to happen). But in Column B, where we record the initials, date and time when a chart is received, the values remain static. When a chart is received, Column B is updated manually, pulling on Column A for the current date/time stamp.
I hope this helps clarify what is updating, and what isn't, and confirms that the spreadsheet is working the same way on both our systems?
Thanks,
Andrea
Just to confirm that in Column A, the lookup column, the time stamps update (which we want to happen). But in Column B, where we record the initials, date and time when a chart is received, the values remain static. When a chart is received, Column B is updated manually, pulling on Column A for the current date/time stamp.
I hope this helps clarify what is updating, and what isn't, and confirms that the spreadsheet is working the same way on both our systems?
Thanks,
Andrea
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Martin, this will do the trick!
Andrea
Andrea
You're welcome and I'm glad I was able to help.
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
ASKER
Thanks, Martin, I'll check them out!
Cheers,
Andrea
Cheers,
Andrea