Calculate End of Month using EOMONTH function

You may not have seen many people using this formula. However, it is as useful as SUM formula at times. During this post, I will guide you about syntax of formula and its uses. Like the title of post says, it is used to find end of month. I will share some very important scenario's and method to find beginning month using same formula.

The function return's end of month and it requires the input as Date. Also, since it return's the date as number you will have to format cell to show dates.

Copy-Paste the table in MS Excel starting from A1.

Date	Month	Formula	Returns
6-Mar-09	-1	=EOMONTH(G11,H11)	28-Feb-09
6-Mar-09	1	=EOMONTH(G12,H12)	30-Apr-09
6-Mar-09	2	=EOMONTH(G13,H13)	31-May-09

Formula: =EOMONTH(StartDate,Months)

In the above example, -1 with 6-Mar-09 will return you the end of Feb. Similarly, 0 will return current month and 1 will return next month.

Example1: You want to find the number of days remaining in current month.

Formula: =EOMONTH(TODAY(),0)-TODAY()

TODAY() will return the current date and zero will tell EOMONTH to consider current month. Difference between EOMONTH(TODAY(),0) and TODAY() will return number of days left in month.

Example2: You want to find the first of current month.

Formula: =EOMONTH(TODAY(),-1)+1

TODAY() will return the current date and -1 will tell EOMONTH to consider previous month. Adding 1 to end of previous month will return 1st of current month.

Kindly let me know your views about post. Subscribe via email to blog to receive latest updates in your inbox.

We assure you knowledge, not SPAM!

Read more on this article...

Datedif function

Datedif is function to find difference in dates. Days360 function also find the difference in days. But the major difference between Day360 and Datedif is Days360 consider 12 months each of 30 days hence its not very accurate. But, this is useful if you have an accounting system which require 12 months each of 30 days. Like in other post I will help you understand the syntax of formula followed by examples.

Formula: Datedif(PastDate, CurrentDate, "INTERVAL")

Interval: Interval can be days, Months, Years, Yearsdays, Yearmonths and monthdays. Mentioned below is description for each type of interval.

Past Date	Current Date	Interval	Formula	Result
24-Nov-03	10-May-08	days	=DATEDIF(A2,B2,"d")	1629
24-Nov-03	10-May-08	months	=DATEDIF(A3,B3,"m")	53
24-Nov-03	10-May-08	years	=DATEDIF(A4,B4,"y")	4
24-Nov-03	10-May-08	yeardays	=DATEDIF(A5,B5,"yd")	75
24-Nov-03	10-May-08	yearmonths	=DATEDIF(A6,B6,"ym")	5
24-Nov-03	10-May-08	monthdays	=DATEDIF(A7,B7,"md")	17

Copy-Paste the table in MS Excel starting from A1 Cell.

I have attempted to give example in another post. I am enclosing the link below. Request you to visit same.

Datedif example

Kindly post your view about post in comments. Also, you can Subscribe to Free MS Excel help by Email to receive latest update about this blog and we will also send free add-ins, e-books and lot right in your inbox.

We assure you knowledge, not SPAM!

Read more on this article...

Concatenate function to join text

Concatenate is one of most useful function is Microsoft Excel. It is used to join content from two cells. Here we will discuss examples of CONCATENATE function, example and alternative method to joined cells.  Let’s take an example, that we have five columns with Address, area, city, State and pin code and we want to join five columns to get complete address in one column.

For example, A14 - Address, B14 - Area,

C14- City, D14- State and E14- Pincode. Then, the formula to concatenate will be as mentioned below

=CONCATENATE(A14, " ",B14," ",C14," ",D14," ",E14)

Another way to achieve same will be by using formula

=A14&" "&B14&" "&C14&" "&" "&D14&" "&" "&D14

Click on image below to see concatenate example in details.

If you like this post, do post your views as comments. Also, you can subscribe via email to receive updates, MS Excel tricks, e-books and add-ins.

We assure you knowledge, not SPAM!

Read more on this article...