VBA Date & Time issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2506424%22%20slang%3D%22en-US%22%3EVBA%20Date%20%26amp%3B%20Time%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2506424%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22D_Jones1994_0-1625136012619.png%22%20style%3D%22width%3A%20696px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292855i8CE17EF8572F9AF8%2Fimage-dimensions%2F696x96%3Fv%3Dv2%22%20width%3D%22696%22%20height%3D%2296%22%20role%3D%22button%22%20title%3D%22D_Jones1994_0-1625136012619.png%22%20alt%3D%22D_Jones1994_0-1625136012619.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3Bso%20the%20above%20VB%20Code%20above%20says%20that%20if%20cells%20A%2C%20B%20and%20C%20are%20completed%2C%20then%20in%20Cell%20D%20the%20date%20and%20Time%20is%20added%20in%20the%20format%20%22DD%2FMM%2FYYYY%20hh%3Amm%3Ass%20AM%2FPM%22%3C%2FP%3E%3CP%3Ebut%20for%20the%20first%2012%20days%20of%20the%20month%20the%20format%20is%20%22MM%2FDD%2FYYYY%22%20but%20once%20it%20turns%20the%2013th%2C%20it%20corrects%20itself%20as%20if%20it%20finally%20registers%20as%20a%20month%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22D_Jones1994_1-1625136182734.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292856i553975BE44475624%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22D_Jones1994_1-1625136182734.png%22%20alt%3D%22D_Jones1994_1-1625136182734.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20for%20example.%26nbsp%3B%20tomorrow%20it'll%20say%2007%2F02%2F2021.%26nbsp%3B%20but%20on%20the%2013th%20it'll%20say%2013%2F07%2F2021.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help%20with%20this.%26nbsp%3B%20it's%20been%20bugging%20me%20for%20months%20%3CLI-EMOJI%20id%3D%22lia_face-with-tongue%22%20title%3D%22%3Aface_with_tongue%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ED_Jones1994%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2506424%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2506527%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Date%20%26amp%3B%20Time%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2506527%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1092983%22%20target%3D%22_blank%22%3E%40D_Jones1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%20%20%20%20Cells(x%2C%204).Value%20%3D%20Now%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

D_Jones1994_0-1625136012619.png

 so the above VB Code above says that if cells A, B and C are completed, then in Cell D the date and Time is added in the format "DD/MM/YYYY hh:mm:ss AM/PM"

but for the first 12 days of the month the format is "MM/DD/YYYY" but once it turns the 13th, it corrects itself as if it finally registers as a month?

D_Jones1994_1-1625136182734.png

 

so for example.  tomorrow it'll say 07/02/2021.  but on the 13th it'll say 13/07/2021.

 

Thanks in advance for any help with this.  it's been bugging me for months

 

D_Jones1994

 

2 Replies

@D_Jones1994 

 

Use

    Cells(x, 4).Value = Now
wow... yeah that's worked. thank you so so much.

(I'm a little embarrassed how easy that solution was)

Thank you