In many cases, you need to calculate the last date of a month based on a given date. If I give you an example then it would be very clear. Assume that, you got a joining date and question is how many days you have worked on that joining month? To find the last date you can take help from EOMONTH() function. But in this article I will show you how you can do that in 3 ways.
Before going to start design your worksheet like below image:
Image 1: Before calculate the last day of the month
1. EOMONTH():
In EOMONTH() function "EO" means "End of". This function needs to supporting values to calculate the last date of the month. First is the "start date", means a date based on you need to calculate the last date of that month. And second is, which month? Here you should remember:
0 = Current month based on given date
1 = Next Month
-1 = Previous Month.
You can use Next 11 months and previous months by using 11 or -11. Ok, enough describing. Now it's time to work. In C7 cell enter the below formula:
This formula will return the last date of that "Feb 2016" month. And while you press Enter, then you will see 29-Feb-2016. If you use -1 instead of 0 (zero), then it will return 31-Jan-2016 and if you use 1 instead of 0 (zero) then it will return 31-Mar-2016.
2. Date() and 0 (Zero):
A common thing about excel we all knew that, Excel treated Date as a numerical value. 1-Jan-1900 date is 1 in numerical value. And then Computer adding 1 after ending a day. In this way 42429 numerical value treated as 29-Feb-2016.
You can calculate the last date by using Date() and 0 (Zero). Just in C7 cell enter below formula:
The DATE() function returns the date of YEAR(C4) = 2016, MONTH(C4) = Feb, MONTH(C4)+1 = March and 0 is the last day of previous month.
The trick is in MONTH(C4)+1 is March and 0 is the last day of previous month. So, formula returns:
=DATE(2016,3,0)
=29-Feb-2016
3. Date() and -1:
Another way is to calculate the last date of a month based on a given date is, in C7 cell enter the below formula:
The DATE() function returns the date of YEAR(C4) = 2016, MONTH(C4) = Feb, MONTH(C4)+1 = March and 1 is the first day of the mentioned ((MONTH(Feb)+1 = March) month. So, it returns
=DATE(2016,3,1)-1
=29-Feb-2016
There are few more ways you can get the last date of the month based on a given date. You will see the below result finally:
In C8 cell use Days() to find days in between 2 dates. From this example, enter the following formula in C8 cell:
This will return 10. It is correct that 29 - 19 = 10 days. But you have to think logically that, that Employee Joined 19-Feb-2016, that means he/she worked that day and the company have to pay for that day also. So in C8 cell 1 day will add like below:
And it returns 11 days.
In C9 cell just use the below formula to calculate the final paid amount (BDT) to the employee after the ending of that month:
And the result is 2805 like below image.
I hope you have learnt these functions as I have clearly described here. The learning functions from this article is: DATE(), YEAR(), MONTH(), DAYS(), EOMONTH().
Best of luck for your work.
Before going to start design your worksheet like below image:
Image 1: Before calculate the last day of the month
1. EOMONTH():
In EOMONTH() function "EO" means "End of". This function needs to supporting values to calculate the last date of the month. First is the "start date", means a date based on you need to calculate the last date of that month. And second is, which month? Here you should remember:
0 = Current month based on given date
1 = Next Month
-1 = Previous Month.
You can use Next 11 months and previous months by using 11 or -11. Ok, enough describing. Now it's time to work. In C7 cell enter the below formula:
=EOMONTH(C4,0)
2. Date() and 0 (Zero):
A common thing about excel we all knew that, Excel treated Date as a numerical value. 1-Jan-1900 date is 1 in numerical value. And then Computer adding 1 after ending a day. In this way 42429 numerical value treated as 29-Feb-2016.
You can calculate the last date by using Date() and 0 (Zero). Just in C7 cell enter below formula:
=DATE(YEAR(C4),MONTH(C4)+1,0)
The DATE() function returns the date of YEAR(C4) = 2016, MONTH(C4) = Feb, MONTH(C4)+1 = March and 0 is the last day of previous month.
The trick is in MONTH(C4)+1 is March and 0 is the last day of previous month. So, formula returns:
=DATE(2016,3,0)
=29-Feb-2016
3. Date() and -1:
Another way is to calculate the last date of a month based on a given date is, in C7 cell enter the below formula:
=DATE(YEAR(C4),MONTH(C4)+1,1)-1
The DATE() function returns the date of YEAR(C4) = 2016, MONTH(C4) = Feb, MONTH(C4)+1 = March and 1 is the first day of the mentioned ((MONTH(Feb)+1 = March) month. So, it returns
=DATE(2016,3,1)-1
=29-Feb-2016
There are few more ways you can get the last date of the month based on a given date. You will see the below result finally:
Image 2: Found the last date of a month based on a given date
In C8 cell use Days() to find days in between 2 dates. From this example, enter the following formula in C8 cell:
=DAYS(C7,C4)
This will return 10. It is correct that 29 - 19 = 10 days. But you have to think logically that, that Employee Joined 19-Feb-2016, that means he/she worked that day and the company have to pay for that day also. So in C8 cell 1 day will add like below:
=DAYS(C7,C4)+1
And it returns 11 days.
In C9 cell just use the below formula to calculate the final paid amount (BDT) to the employee after the ending of that month:
=C8*C5
And the result is 2805 like below image.
Image 3: Final result
I hope you have learnt these functions as I have clearly described here. The learning functions from this article is: DATE(), YEAR(), MONTH(), DAYS(), EOMONTH().
Best of luck for your work.