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
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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
0
AndreamaryAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Roy CoxGroup Finance ManagerCommented:
The time stamp update when the sheet calculates. Try hitting Calculate Now in the formula tab
0
AndreamaryAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
Why not set the width of the column such that it just shows the part of the value that you want to see?
0
AndreamaryAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
At the end of those macros add

    Columns("B:B").Select
    Selection.ColumnWidth = 14
0
Martin LissOlder than dirtCommented:
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.
0
AndreamaryAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
The Timestamps update on the downloaded example.
0
AndreamaryAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
Sub UnhideALLColumn()
'
' UnhideALLColumn Macro
'

'
    Columns("A:FB").Select
    Selection.EntireColumn.Hidden = False
    Columns("B:B").Select
    Selection.ColumnWidth = 14
    Range("A1").Select

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Thanks, Martin, this will do the trick!
Andrea
0
Martin LissOlder than dirtCommented:
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
0
AndreamaryAuthor Commented:
Thanks, Martin, I'll check them out!
Cheers,
Andrea
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.