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?
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Roy CoxGroup Finance ManagerCommented:
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.