CODEWITHSUNDEEP
excelvbadatetime
Martin H
Martin H

Reputation: 749

VBA Date + TimeValue returns no time

I have a date and time which I assemble into a date + time from strings in the form

date_string = "2020-12-30" 'yyyy-mm-dd    
date_code = CDate(date_string)

time_string = "00:00:00" 'hh:mm:ss
time_code = TimeValue(time_string)

date_time = date_code + time_code

Commonly the return looks like 05.01.2019 11:00:00, which is what I expect.

The returned values also all check out as TRUE if I test with IsDate(date_time)

Whenever the time is 00:00:00 however, I only get the date returned with no time appended. I dont quite understand this, since TimeValue(time_string)returns 00:00:00.

So it must be an issue when combining date and time to a date + time string.

Can someone please enlighten me why midnight somehow does no exist in Excel VBA or where my error in creating the time code is?

EDIT:

I try to explain my situation a bit better:

I do this date date/time stuff in code and then but the result in an array in a loop. Only later on it is written to a cell in a table.

By the time is is written into a cell, even custom formatting the cell to "DD.MM.YYYY hh:mm" does not show the time as it is completely missing from the cell value.

Do I neet to apply a format at the point of date_code + time_code?

Upvotes: 0

Views: 1584

Answers (2)

Martin H
Martin H

Reputation: 749

Sometimes the answer can be so simple. Thanks to Variatus and Paul I checked formatting out.

I applied a date_time = Format(date_code + time_code, "dd.mm.yyyy hh:mm") in my code. Using this, my code runs as expected and 00:00:00 appears as expected, even in the cell values of the Excel table.

Upvotes: 1

Variatus
Variatus

Reputation: 14373

When you enter an integer, like 43930, in a cell Excel will record the number as an integer, just as you entered it. You can then proceed to format the cell as #,##0.000 and thereby make the number display as 43930.000. Or you can format that very same number as custom dd mmm yyy hh:mm:ss and display it as 09 Apr 2020 00:00:00. The point is that Excel chose to record the number in its most efficient way, as an integer.

So, if you enter a DateValue + TimeValue which, together, amount to an integer Excel will record the integer correctly. The format in which that integer is displayed in your worksheet is a matter for cell formatting.

Upvotes: 0

Related Questions