Link to home
Start Free TrialLog in
Avatar of Andreamary
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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of Andreamary
Andreamary

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
The time stamp update when the sheet calculates. Try hitting Calculate Now in the formula tab
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
Why not set the width of the column such that it just shows the part of the value that you want to see?
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
At the end of those macros add

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

Sub UnhideALLColumn()
'
' UnhideALLColumn Macro
'

'
    Columns("A:FB").Select
    Selection.EntireColumn.Hidden = False
    Range("A1").Select
End Sub

Open in new window


Thanks!
Andrea
The Timestamps update on the downloaded example.
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
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, Martin, this will do the trick!
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
Thanks, Martin, I'll check them out!
Cheers,
Andrea