Công thức và cách thực hiện phép tính với ngày, tháng trong Excel

Nguồn bài viết: Công thức và cách thực hiện phép tính với ngày, tháng trong Excel

In the previous post, I showed you how to calculate the time between two different times to help you calculate the number of working hours in a day, the flight time of a flight is very easy…

Yes, and as promised, in this article I will talk about how to calculate days, months, and years in Excel!

I have a New Product Launch Plan table with 6 working stages and I want to know how many working days each phase has.

Method #1: Calculate the number of days between 2 times using NETWORKDAYS

This way will calculate the number of working days in the week from Monday to Saturday, Sunday is considered a holiday and will be omitted. If there is a public holiday, it will not be counted.

Perform:

– Place the mouse pointer in the box D3 (Where do you want to calculate the number of days of Market Research activity)

– Left click on fx (Insert Function)

– At the box Insert Funtionplease set the following:

• Select a category: Select Date & Time
• Select a function: Select NETWORKDAYS
• Press Ok to agree.

– At the box Funtion Arguments You set the following:

• Start_date (Start date): You enter B3
• End_date (End date): You enter C3
• Holidays (Holidays): You enter F3:F5

Note:

– If there is a public holiday from the start date to the end date, select it, if not, leave it blank.

– If there is a public holiday, choose 1 day, if there are two days, you can choose the address of the range of cells.

Working days are counted from Monday to Saturday, Sunday is a day off. If the holiday coincides with a Sunday, Excel will consider it as a day off (But there is no compensatory rest on the 2nd day like in Vietnam). In the example, April 30 falls on a Sunday, although there are 3 holidays, Excel only counts 2 days.

The result after doing all the cells in turn, we get the following:

Method #2: Calculate the number of days between 2 times using DATEDIF

This way finds the number of days difference between two times and of course it also includes weekends and public holidays

The command is as follows:

• =DATEDIF(start date; end date;”y”) : If you want to calculate by year.
• =DATEDIF(start date; end date;”m”): If you want to calculate by month.
• =DATEDIF(start date; end date;”d”): If you want to count by date.

NOTE: See detailed instructions on how to use the DATEIF function: Calculate the number of days, months, and years between any two times on Excel

The results when using the Datedif command are as follows:

You notice how the two numbers of days are much higher than the first way because it includes Sundays and weekends.

Epilogue

Okey, So I have shown you two ways to calculate the number of days, months and years in a period of time.

With the number 1 method, it will help us to count the actual number of working days minus weekends and holidays in the year.

The second way helps us to know the exact number of days between two different times. Depending on what your purpose is to choose the appropriate calculation.

If you find the article interesting, do not hesitate to share it with your friends, please support blogchiasekienthuc.com by recommending this site to everyone.

Hope this article will be useful to you. Good luck !

Contributor: Nguyen Xuan Ngoc

The article achieved: 5/5 stars – (There are 2 reviews)

Note: Was this article helpful to you? Don’t forget to rate the article, like and share it with your friends and family!

Những bài viết liên quan : Thủ Thuật Excel

Nguồn : congthucexcel.com

Mọi sự sao chép, trích dẫn vui lòng ghi rõ nguồn: congthucexcel.com

from congthucexcel.com https://ift.tt/5J8s2LF
via Congthucexcel.com