China Naming Network - Ziwei Dou Shu - Excel table formula of school calendar week and date

Excel table formula of school calendar week and date

Dates entered in standard form can be directly subtracted, 2013/2/3-2013/2/1= 2.

How many years, months and days will it take to return the difference of more than days? The reference formula is as follows:

=DATEDIF(A 1,B 1," y ")& amp; "Year"&; DATEDIF(A 1,B 1," ym ")& amp; "Month"&; DATEDIF(A 1,B 1," MD ")& amp; Heaven 1. The idea of realizing this formula:

1. Calculate from the initial time set by the computer (1900 65438+ 10/Sunday) to the current date of making the form (assuming it is 20 1 1 September1day).

2. Calculate the remainder of the total number of days divided by the number of days in 7 weeks.

3. Calculate the total number of days MINUS the remainder, so that the total number of remaining days is a multiple of the number of days in a week, then the last day is Saturday.

4. Calculate the new total days after adding 1 to the remaining total days, so the last day is Sunday.

5. Convert the new total number of days into a date as the start date of the first week (Sunday), and add 6 to the new total number of days to convert it into a date as the end time of the first week (Saturday). In this way, the first week's time slot filling is realized.

6. In order to automatically add 7 to the start and end date of the second week (plus the days of the week), you can read the line number, multiply it by 7 as a variable parameter, add it to the total days of the new start and end time, and then complete the filling of the second week. However, the line number parameter can add or subtract the corresponding natural number to adapt to the calendar title.

7. Fill in the number of weeks you want to arrange in the formula of the first week.

8. Change the current date, and the start and end dates of all weeks will be automatically changed and filled in.