Cell time format

[trevolly]

Hello all,

I have a worksheet and for selected cells I have set up a vba code so that when you double click in these cells it enters the current time. The vba code is set up to minus 1 hour (zulu time). I have also formatted the cell to "custom - hh:mmz". This works fantastically with the double click entering the time only in the format 13:25z for example. With zulu time ending soon I am preparing the sheet for this. The minus time part is easy (just deleting part of the vba code.)

The issue I am having is in formatting the cell time format. Whenever I select "hh:mm" from the "Custom" list in the cell format and then double click I get ###### showing. If I extend the width of the column it is inputting the date AND time where in the previous setup (zulu hh:mmz) it only inputs the time on double click.....

If I go to "Custom" on the format cell list and enter hh:mm and put a full stop after the hh:mm then it only enters the time in to the cell. Am I missing something with the formatting ?? I thought it would just be select hh:mm. I'm after the cell showing the existing time in the cell as 13:25 for example.

The "double click" vba code is.... with me just deleting the " - (1/24), giving me the normal time in the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp with zulu
If Not Intersect(Target, Range("B4:B249")) Is Nothing Then
Cancel = True
Target.Value = Now() - (1 / 24)
End If
End Sub

The screenshot below shows the ###### B5 cell which is the format cell set as hh:mm. The B6 cell format is hh:mmz. The second table is the the column extended showing how its entering the cells differently on double click.

Thanks all
T

 

[Fluff]

If your existing format works, why not leave it?

 

[trevolly]

Because I need to remove the zulu input for the end of the month when the UK clocks go back an hour. Its just removing the "z" as shown in cell B6 in the screenshots. but as I put in my first post as soon as I change the cell format from hh:mmz to hh:mm it enters the date and time on double click.

 

[Fluff]

But it's still zulu time so why does it matter if you have the z at the end?
That said you can add this line

Target.NumberFormat = "hh:mm"

after you insert the date & time to format the cell.

 

[trevolly]

I understand what you're saying about zulu time and i agree but my bosses have asked if it can be removed when the clocks change to avoid confusion. Not sure where you mean to put the above code...... I'll just stick to a full stop on the end of the hh:mm cell format.

Thanks for your time

 

[Fluff]

You just need to put that line before the End If

 

[trevolly]

Thank you @Fluff, that did it . Sorry to be a pain (its annoying when your employer is turning to you to do it instead of the IT dept) and I really appreciate your help.

 

[Fluff]

You're welcome & thanks for the feedback.