Cell time format

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • both.jpg
    both.jpg
    51.9 KB · Views: 6

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
If your existing format works, why not leave it?
 

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Target.NumberFormat = "hh:mm"
after you insert the date & time to format the cell.
 

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
You just need to put that line before the End If
 
Solution

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,632
Messages
5,742,228
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top