[Go] "VBA study" date and time-related calculations in VBA

Iamlaosong

http://blog.csdn.net/iamlaosong/article/details/18458253

The function that takes the current date in VBA is date, the function of the current time is now, and the current date and time are now. the functions for converting dates to years and days are: year, Month, day, values that can be taken with date or now. The functions of time conversion to minutes and seconds are: Hour, Minute, Second, the value that the parameter can be taken out of or now.

For example:

MsgBox "Current time:" & Now & Chr & _ "When:" & Hour (present) & Chr (Ten) & _ "Points:" &            Minute (now) & Chr (Ten) & _ "Seconds:" & Second (current) MsgBox "Present Date:" & Today & Chr (Ten) & _ "Year:" & Years & Chr & _ "Month:" & Month (now) & Chr (Ten) & _ "Day:" & Amp Day (now)

1. Date Conversion

Very simple, directly with the conversion function can be, for example: CDate ("2012-12-31")

2. Number of days between dates

With the DateDiff function, note that the function name in the worksheet cell is datedif, one f is missing, and the parameter order is different (=datedif (A2,B2, "D")). For example:

DT = Cells (2, 1) ' cell contents: 2013-1-1 09:35:08

DD = DateDiff ("D", CDate ("2012-12-31"), DT)

The "D" can also be converted to other letters, in order to calculate the other time difference (month and day, etc.), such as "W" to calculate the number of weeks between two dates,"H" represents the calculation of the number of hours between two dates, see the Appendix at the end of this article.

The simplest of days between dates is the direct subtraction, if the date contains time, subtract is two datetime value subtraction, the result is a floating point number (time difference), the calculation days need to use datevalue conversion (equivalent to remove the fractional part of rounding) before the calculation, for example:

DT = Cells (2, 1) ' cell contents: 2013-1-1 09:35:08

DD = DateValue (DT)-CDate ("2012-12-31")

In fact,the value of the DateValue function is the number of days that are converted from 1900-1-1 plus 1, which means that the DateValue function value of the date 1900-1-1 is 1, and the date before this date is not recognized by Excel. The date and time essence is a floating-point number, the integer part is the date, and the fractional part is the time.

3. Time difference Calculation

The time difference calculation is usually converted to a time value using the TimeValue () function and then calculated. For example:

DT2 = Cells (2, 1) ' cell contents: 2013-1-1 09:35:08

DT3 = Cells (3, 1) ' cell contents: 2013-1-1 11:55:18

Interval=timevalue (DT3)-timevalue (DT2)

The value of TimeValue is between 0-1 and 0-24 hours, which is actually the ratio of 24 hours to the current time. This value can be used to do a lot of things, for example, the time value multiplied by 24 and then rounding is the time from 0 o'clock hours, multiplied by 24*60 and then rounding is the time from 0 o'clock minutes value, multiplied by 24*60*60 is the time from 0 o'clock seconds value. For example, with 10 minutes as the interval, the number of intervals in a day is 144, then any time value multiplied by 24*6 and then rounded to get the number of this interval starting from 0 o'clock.

Note:The data type of the TimeValue is single, and if only the time is entered in the cell, it is also assigned to the single type variable. For example:

tt = Cells (2, 1) ' cell contents: 12:00:00, value of TT after assignment is 0.5

We calculate two time difference, that is, calculate the difference of two floating-point numbers, such as the program's running times, the difference is multiplied by 24 is the difference in hours, multiplied by 24*60 is divided into units, multiplied by 24*60*60 is in seconds.

The time can also be subtracted directly, but the data type must be correct, and the effect is the same as using the TimeValue function.

4, about rounding

INT (12.56) = 12: Directly takes the integer part, CInt (12.56) = 13: Fractional part rounded;

Ceiling (12.56,1) = 13: To the upward, as long as there are decimals, the integer part is added 1,ceiling (12.05,1) = 13, of course, this function is more powerful, rounding is just an application;

Round (12.56,0) = 13: Fractional rounding, similarly, the function has two parameters, rounding is just an application.

5, about the round function rounding

The round function is rounded not every 5, for example: round (0.5) =0, round (1.5) =2, round (2.5) =2, round (3.5) = 4, round (4.5) =4, difficult to even odd even? The answer is that it is true that the round function is "banker rounding" in VBA, and it is recommended that you use the round function carefully in VBA for rounding. What is "banker rounding", defined as follows:

" four six into five considerations, five after the non-zero into one, five after the zero look at parity, five ago for I should give up, five before the odd to enter a." This rounding method is an international standard, and most programming software uses this method, which is said to be generally used internationally.

If you are rounding in Excel VBA, you can also call the Excel worksheet function directly to achieve the purpose of direct rounding Application.round (A, b)

===============================

attached: DateDiff function Usage

DateDiff (interval, Date1, date2[,firstweekofyear[,firstweekofyear]) returns a Variant (long) that represents the number of time intervals between two specified dates Interval: Sets the unit of the period calculation between two dates. For example, >interval= "m" means the calculated unit is month. >interval set values such as: yyyy > Year q Quarter quarter m month D Day W Weekday week h Hour n Minute min

s Second sec Date1, Date2: Two date expressions during calculation, if >date1 earlier, the period between two dates is positive, and if >date2 is earlier, the result is negative. firstweekofyear: Set the first day of the week to a few days, if not set as Sunday. The setting values for the >FW are as follows: 0 use the >api setpoint. 1 Day 2 Week 13 Tuesday 4 week 35 Thursday 6 week 57 Saturday firstweekofyear: Set the first week of the year, if not set, the week of January 1 is the first week of the year. The setting values for the >fy are as follows: 0 use the >api setpoint.    1 January 1 that week for the first week of the Year 2 includes at least four days of the first week for the first week of the Year 3 includes seven days of the first week for the first week of the year the example is as follows: Calculates the number of months between two dates. DIFFMONTHL = DateDiff ("M", Date_start, Date_end)

[Go] "VBA study" date and time-related calculations in VBA