Manipulation of dates and times in VBA

[Cummins]

When you are in in excel and have a cell with a date like 11/23/11 13:10:34, and you change the cell to numeric, the value of the cell changes to 40870.55. This number is obviously easy to manipulate and convert back to a date format. How can you do the same thing in VBA. I have not seen any discussions on this (that I could find). Can someone chime in? My goal is to add and subtract dates and times without tons of lines of code. The largest issue I have seen with using the standard date format is that it doesn't work well when you cross days/24:00.

 

[mjbeam]

x = DateValue("1/22/2012")
y = TimeValue("12:22:32")
z = x + 2 + y + (1 / 24)

'z is now equal to 1/24/2012 1:22:32 PM

 

[Leith Ross]

Hello Cummins,

The Date/Time value is stored internally as a Double type number in both Excel and VBA. The integer portion represents the date from January 01, 1900. The fractional portion is the number of seconds that have elapsed since midnight. How it appears is a function of formatting. VBA has quite a few built-in functions for handling Dates and Times. You should consult the help files for more details on what is available and how to use them.

 

[Cummins]

Understood. I've dug through all of the VBA help files already. My issue is that the way I would like to approach the problem is extremely simplistic and short. I have seen many examples as is being suggested by "mjbeam" but there is nothing that explains how to or how it is not possible to approach it from my view point. Is it really that difficult to change to a decimal format from a date format within VBA as compared to excel?

 

[mjbeam]

I showed you some examples above. What exactly are you trying to do? What is your "viewpoint?"

 

[Cummins]

If it is possible to represent Date/time as a decimal format number in excel, why is it not possible to do the same thing in VBA...or is it not possible which is why we end up performing complex manipulation of days/hours/Min and Seconds?

 

[mjbeam]

x=datevalue("1/22/2012")

x now contains 40930. It will display it in the debugger as "1/22/2012" but it contains the value 40930. If it bothers or confuses you that it is displayed as "1/22/2012" try doing it this way:

x=datevalue("1/22/2012")*1



y=timevalue("1:34:23 PM")

y now contains 0.565543981481481. It will display it in the debugger as "1:34:23 PM" but it contains the value 0.565543981481481. Multiply it by one if this bothers you.



z=x+y

z is now equal to 40930.565543981481481, which if represented as a date is "1/22/2012 1:34:23 PM"

If we add 1 to z, it will be equal to 40931.565543981481481, which if represented as a date is "1/23/2012 1:34:23 PM"


And although my profile says Portland, Oregon, I actually live in Forest Grove but am at work right now in Hillsboro. So if this isn't making any sense maybe we can discuss it one of these days at a Starbucks over a cup of coffee, lol.

 

[Cummins]

Bless you in advance. I have seen Datevalue and timevalue but I have not seen it used in practical applications like this. I am not sure why I have not seen this represented in discussions of time/date manipulation threads as (to me) it seems the easiest way to perform the math without errors in code etc... Kudos and a Gold Star to you my friend!