20161106 times a day how to automatically update as of yesterday's date-CodePudding

 Sub tests () 
'
'test macro 
'

'
Range (" B1 "). Select 
ActiveSheet. PivotTables (" pivot table 1). PivotFields (" dimension [time]. [hierarchy] [Deal Year] "). _ 
VisibleItemsList=Array (" ") 
ActiveSheet. PivotTables (" pivot table 1). PivotFields (" dimension [time]. [hierarchy] [Deal the Month] "). _ 
VisibleItemsList=Array (" dimension [time]. [hierarchy]. [Deal the Month]. & amp; [201608] ") 
ActiveSheet. PivotTables (" pivot table 2 "). PivotFields (" dimension [time]. [hierarchy] [Deal Year] "). _ 
VisibleItemsList=Array (" ") 
ActiveSheet. PivotTables (" pivot table 2 "). PivotFields (" dimension [time]. [hierarchy] [Deal the Month] "). _ 
VisibleItemsList=Array (" ") 
ActiveSheet. PivotTables (" pivot table 2 "). PivotFields (" dimension [time]. [hierarchy] [Deal Date] "). _ 
VisibleItemsList=Array (" dimension [time]. [hierarchy]. [Deal Date]. & amp; [20161106] ") 
End Sub

CodePudding user response:

The DateDiff function

Returns the Variant (Long) the value of the said two day period specified time interval number,

Syntax

DateDiff (interval, date1 and date2 [, firstdayofweek [, firstweekofyear]])

DateDiff function grammar has named parameters of the following:

Part of the description
The interval is necessary, string expression, said the time interval is used to calculate the time difference of date1 and date2
Date1 date2/necessary; Should be used in the calculation of the Variant (Date), the two dates,
Firstdayofweek optional, constant specified on the first day of a week, if not specified, the first day is Sunday,
Firstweekofyear optional, specified in the first week of a year of constant, if not specified, are to contain the week for the first week of January 1,

Set the

The interval parameter value is as follows:

Set the description
Yyyy years
Q season
M on
Y a year of days
D day
W week days
Ww week
H
N minutes
S second

Firstdayofweek parameter value is as follows:

Constant value description
VbUseSystem use NLS API set 0,
VbSunday 1 Sunday (the default)
Monday vbMonday 2
VbTuesday 3 Tuesday
On Wednesday, vbWednesday 4
VbThursday 5 Thursday
VbFriday Friday 6

vbSaturday 7 Saturday
Constant value description
With NLS vbUseSystem API set,
VbFirstJan1 1 from January 1, contains week (the default),
VbFirstFourDays 2 from the first of its more than half a week in the New Year began a week,
VbFirstFullWeek 3 from the first week without the maximal,

Description

DateDiff function can be used to determine the number specified time interval between two dates, for example, you can use DateDiff to calculate the interval of days between two dates, or calculated from today until the end of the year and how many weeks,

In order to calculate date1 and date2 are days, you can use "the days of a year" (y) or (d) "day", when the interval is "days of the week" (w), DateDiff returns the number of weeks during the two days, if date1 is Monday, DateDiff until date2 Monday the number of calculation, the number contains no contain date1 date2 but, however, if the interval is "week" (ww), the DateDiff function returns the "calendar week" during the two days, by calculating the number of the between date1 and date2 Sunday and, if date2 is just on Sunday, is date2 will also be added to the DateDiff count results; But whether date1 is Sunday, don't put it into account,

If date1 date2 came later, DateDiff function return value is negative,

Firstdayofweek parameters affect the use of interval symbol "W" or "WW" computing results,

If date1 and date2 is the date of words, then specify the year as the date of the fixed part, however, if date1 and date2 enclosed in double quotation marks (" "), and the year is slightly and not to mention, in every time calculation expression date1 and date2, the current year will be inserted into the code, so you can write program code is suitable for different years,

In calculation on December 31, and next year on January 1, poor, DateDiff return 1 said differs a year, although in fact only a day,

CodePudding user response:

I want to every day is automatically updated to yesterday's data, the background of the connection is a multidimensional database, the database has the dimension of time