Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

12:00 PM showing as 12:05 AM

0

I have one more problem with my Catering Management System.

I am inputing 12:00 PM into the "Main Meal Service Time" and when the record is added, the Database shows it correctly.

If a recall the record to make amendments to the Function Details, it brings the Time in as 12:05 AM, and I cannot understand why!

All cells are formatted as TIME and I know that Excel doesn't handle 12:00 PM (Noon) very well. I have tried many VBA lines of code to fix the issue, but to no avail.

Can someone please assist me?

I have uploaded screenshots in a PDF and the Workbook itself.

excel
posted by: tonyimbruglia (rep: 6) on Sat Dec 17, 2022 at 10:41 pm
Answer
Discuss

Discussion

Tony

I answered your question but I couldn't see the pdf you refer to (since you can only upload Excel files in this Forum). For future questions, you could embed the pdf within your Excel file (and tell us where to find it). An explanation of how to replicate the error would have been handy too (you always can edit your original question to add that).
John_Ru (rep: 6152) Dec 19, '22 at 3:53 am
Add to Discussion

Answers

0
Selected Answer

Tony

The trouble is that your details array stores that time as a Double number (which then messes up ComboBox3 etc.) I fixed it by adding the line in bold below which corrects that element of the array

Private Sub DisplayFunction()



     Call TurnAutomationOff



     Dim row As Long

     Dim i As Integer

     row = wsControls.Range("D3").Value



     ' Populate Database Array with selected Function

     Dim details As Variant

     details = wsDatabase.Range("A" & row + 2 & ":" & "CK" & row + 2)

    ' correct time (convert from Double to Date format)

    details(1, 3) = CDate(details(1, 3))

     ' Populate Input Form with Selected Function

     wsInput.Unprotect

     With wsControls

     '<< remaining code>>

E.g. Sarah's event now shows 12:00 PM

Hope this fixes things for you.

posted by: John_Ru (rep: 6152) on Mon Dec 19, 2022 at 7:23 am
Discuss

Discussion

Thanks once again John. It seems weird that a Time requires CDate, which is described as "the CDATE function converts a value to a date".

Regardless, it worked.
tonyimbruglia (rep: 6) Dec 20, '22 at 5:27 pm
Glad it worked for you, thanks for selecting my answer, Tony. It might seem odd to use CDate but VBA's Date datatype covers both date and time values; there is no separate in-built datatype for time.
John_Ru (rep: 6152) Dec 20, '22 at 6:16 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login