I have a macro that sets a cell to a date value pulled from a task object from the MS Project API like this:
Sub Example(t as MSProject.Task, row As Integer, column As Integer)
dim dt as Date
dt = t.Start 'start date of a Microsoft Project task being accessed via the MS Project API
ActiveSheet.Cells(row, column) = dt
End Sub
the regional date format follows a day - month - year convention so the value of dt
can be something like this: #13/03/2023 08:00:00#
I've set the number format on the cells I'm writing to as this: "dd/MM/yyyy", so I would expect the date value to appear to look like this: 13/03/2023 (Including the time in formula bar but not on the display text of the cell).
However the time of day seems to always be displayed in the cell when the day value is greater than 12. otherwise, it follows the correct format. Here's an example of what I'm seeing:
The time of day will disappear from the cell when I enter and then exit edit mode in the cell (either by clicking into the formula bar or clicking F2 on my keyboard).
Here's a video: https://www.loom.com/share/818e0f287018461a8eb6d726ff8e244b?sid=dd367cd7-a0bd-43ef-8c85-38ea78afd696
What gives? how can I prevent this and make the time value not be displayed in these cells when the day value is greater than 12?
I've tried just converting the date variable to a string value using the cStr()
method but Excel always ends up storing the value as a date in the cell and flips the day and month values whenever the day value of the date variable is less than 12. So 06/03/2023 (6th of March, 2023) becomes 03/06/2023 (3rd of June, 2023), which obviously is a problem.
I've also tried converting the date variable into a long but when I do this I lose the time, and I want to include the time in the cell's value, I just don't want it to be displayed on the text of the cell if the number format does not include the time.
It seems the only way to get around this is to touch each cell. For example:
Dim c As Range
For Each c In ActiveSheet.Range("A2:B7")
Dim d As Date
d = CDate(c) + 0
c = d
Next c
The only way I could replicate the issue was to use the Task StartText property (e.g. t.StartText
) rather than the task Start property (t.Start
). However, using that gave the same results as shown in the question so with luck this solution will work.
It's worth noting that while working within the user interface, adding 0 to the whole range at once using PasteSpecial: Add worked but doing so in vba did not.