Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I am refining how my digital clock displays on my worksheet. Rather than one formatted time display ("h:mm AM/PM"), I am approaching it now with one cell hosting the hours (N2), another the minutes(Q2), and a third the AM/PM (S2). I have a blinking ":" in P2 synced with the seconds (from this earlier inquiry) .
On My worksheet,
F2 is formatted as "mmmm dd, yyyy" and displays the current date properly. (October 9, 2020)
B2 is formatted as "h:mm AM/PM" and displays and updates the current time accurately. (3:19 PM)
N2 is formatted as "h" and appears to be displaying the current hour accurately (3)
Q2 is formatted as "mm" (to display the current minutes), but is not displayed accurately. It is displaying 01, not 19.
S2 is formatted as "AM/PM" and is displaying the appropriate value (PM)
S6 is formatted as "ss" and is displaying the incrementing seconds. (02)
When I click on cell N2, the current time displays in the formula bar. However, when I click on Q2, the formula bar displays "1900-01-00 3:19:02 PM"
What I believe to be happening, is my cell formatting of Q2 as "mm" is displaying the value of the month (01), rather than the minutes (19). (although the month is wrong, I would think it should be 10 ... I'm not sure where the date of 1900-01-00 is coming from when NOW is being used. It should be 2020-).
What must I do to overcome this?
Code:
Public Sub UpdateTimer()
With wsfront
.Cells(2, 6).Value = Format$(Now, "mm/dd/yyyy") '1
.Cells(2, 2).Value = Format$(Now, "hh:mm:ss") '2
.Cells(2, 14).Value = Format$(Now, "hh:mm:ss")
.Cells(2, 17).Value = Format$(Now, "hh:mm:ss")
.Cells(2, 19).Value = Format$(Now, "AM/PM")
.Cells(6, 19).Value = Format$(Now, "ss")
End With
Application.OnTime Now + TimeSerial(0, 0, 1), "UpdateTimer"
End Sub
On My worksheet,
F2 is formatted as "mmmm dd, yyyy" and displays the current date properly. (October 9, 2020)
B2 is formatted as "h:mm AM/PM" and displays and updates the current time accurately. (3:19 PM)
N2 is formatted as "h" and appears to be displaying the current hour accurately (3)
Q2 is formatted as "mm" (to display the current minutes), but is not displayed accurately. It is displaying 01, not 19.
S2 is formatted as "AM/PM" and is displaying the appropriate value (PM)
S6 is formatted as "ss" and is displaying the incrementing seconds. (02)
When I click on cell N2, the current time displays in the formula bar. However, when I click on Q2, the formula bar displays "1900-01-00 3:19:02 PM"
What I believe to be happening, is my cell formatting of Q2 as "mm" is displaying the value of the month (01), rather than the minutes (19). (although the month is wrong, I would think it should be 10 ... I'm not sure where the date of 1900-01-00 is coming from when NOW is being used. It should be 2020-).
What must I do to overcome this?