Tanabata romantic date "Hui"

Super Member Lifetime Card 23% off for a limited time

The original price is 168. Sign up today for only 129!

Don't miss it. ヾ(◍°∇°◍)ﾉﾞ

"Tomorrow is tomorrow, there are so many tomorrows. I was born for tomorrow, and everything will be wasted."

Hello everyone~ The ginkgo leaves on the campus have turned yellow, and they are falling to the ground; we can't stand the cold, and we are also wrapped in thick coats... 2021 has also entered the last busy stage, and the years are in a hurry. !

The editor brought you several practical time function tutorials today, learn these, the time is in your grasp~

1. How many days have passed in a year

Time is life, time is speed, time is power!
How do you know how many days have slipped by this year until today?
As shown in the figure, column A is the date, and column B needs to get the "date" which is the day of the year.

Enter the formula in cell B2, see the following animation

=A2-DATE(YEAR(A2),1,0)

Interpretation Jun:
DATE(YEAR(A2),1,0) part: use the DATE+YEAR function to get the date of the "zero" day of this year, the third parameter can also write "1", but the end must be "+1" ", that is, "=A2-DATE(YEAR(A2),1,1)+1".
Then use A2 (specified date) to subtract the "zero" day of the year, and you're done!

2. Automatically truncate the date and time

The date and time in column A are in the same cell. Now you need to split the date and time into two columns, as shown in the figure below~
Some friends may say, "This is very simple, <Ctrl+E> or the column is done. Well!", here, the editor gives you a like~ but the editor wants to share the function method, because the function can realize data automation, and can be updated with the data source, let's come together!

In cell B2, enter the formula:
=TRUNC(A2),
then select column B, right-click, select [Format Cell] - [Custom], set the type to "yyyy/mm/dd", and you're done!

Split the time in the same way, enter the formula in cell D2

=A2-TRUNC(A2)

Then select column D, right-click, select [Format Cells] - [Custom], set the type to "hh/mm/ss", and you're done!

Animated pictures~

3. Count how many happy Sundays there are

The brick mover is most eager to have a holiday, so do you know how many happy weekends you have spent so far~

As shown in the figure below, calculate how many Sundays there are from the start time 2021/1/1 to the end time 2021/10/31.

Enter the formula in cell B5:
=SUM(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=$A$5))

Interpretation Jun:
ROW(INDIRECT(A2&":"&B2)) part: Use ROW+INDIRECT to construct a date sequence from 2021/1/1 (start time) to 2021/10/31 (end time)

Then use the WEKKDAY function to get the day of the week for each day of the date sequence, and finally match the A5 cell, which is 7 (Sunday) and return TRUE, otherwise return FALSE.

Finally, use the N function to return TRUE to 1, FASLSE to return 0, and add the results to get the number of Sundays!

4. Which quarter is today?

As shown in the figure, column A is the date, and now you want to return the corresponding quarter in column B, what would you do?

Enter the formula in cell B2:
=MATCH(MONTH(A2),{1;4;7;10},1)

Next, let's do "Transfiguration", let's select column B, right-click, select [Format Cells] - [Custom], set the type to "#Quarter", OK!
The animation is up~

5. What is your random TIME

We know that the RAND function and the RANDBETWEEN function can generate random numbers within a certain range, but do you know how to generate random time? Is it some mysterious function?

In fact, it is not the case, you can do it by using the time function and the random function!

As shown in the figure, we want to generate a random time between 8:00:00 and 12:00:00~

Enter the formula in cell A1:

=TIME(8,240*RAND(),61*RAND())

Fill right to left

If you only want to copy the random value generated by the function, first select the data area, hold down the right mouse button, click the "green frame" to pretend to drag it to the side and then drag it back, select [Copy Value Only] in the pop-up menu bar, Oukeke ~

With the development of the times, the rhythm of life has accelerated, and my leisure time has become less and less... When it is time to move bricks, it is better to play secretly with guilt.
Lu Xun once said: "Wasting one's own time is equal to chronic suicide, and wasting other people's time is equal to making money and killing one's life."
Come on, young people full of enthusiasm~~~

That's all for today's sharing. If the tutorial is useful to you, I hope you will share and support the editor! Every like and repost of yours is the motivation to support the editor to insist on originality.

Why choose us, the platform advantage is there

Advantage ①: VIP can learn all tutorials, unlimited learning + supporting material source files + unlimited downloads

Advantage 2: Support WeChat public account + applet + APP + PC website for multi-platform learning

Advantage 3: Support offline viewing, double-speed viewing, allowing you to learn anytime, anywhere

Advantage ④: VIP logo, distinguished status + independent after-sales group answers

★ FAQ ★

①Xiao Ai: Can you watch all the courses after buying VIP ?

Well, yes, including future updates (thanks for your support, we will insist on high-quality teaching updates)

②Xiao Ai: Hello, how do you study ?

Pay attention to the Aizhiqu Education Service Account, you can directly enter the official website to learn from the menu navigation, or you can enter the applet and download the APP!

Computer login to https://www.92zhiqu.com

(Mobile phone, computer learning is essential)

③Xiao Ai: what to do if you do n’t understand

Provide after-sales answers, pay to contact customer service and join the group (pay to contact WeChat customer service, just take a screenshot)

④Xiao Ai: Provide video courseware materials ?

Well, original high-definition video, these are provided (original tutorial, these are the most basic requirements)

⑤Classmate Xiao Ai: Validity period of study ?

Annual membership and lifetime membership, unlimited lifetime membership (self-built website + original tutorial + Aichiqu brand guarantee)

⑥ Primary school students: Does the website support accelerated viewing ?

kindness. At present, mobile phone applet + APP + computer PC all support 1.5 times accelerated viewing or slow playback

⑦ Does the course support offline download ?

kindness. Both Android and Apple IOS APP support mobile phone offline download

Computer downloads are provided by contacting WeChat customer service 18074746783.

⑧ How about the quality of the course ?

Aizhiqu Education high-quality video as the starting point, high-definition subtitles, support for audition

If there is anything else you need to consult, please contact WeChat customer service 18074746783

Long press to identify the QR code "Add Customer Service"

Click to read the original text , log in to the official website with one click, and learn from a large number of video VIPs! (you can try it out)