End Date of The Month : EOMONTH Function

Suppose we have to get the number of days from a certain days given few months and the day will be end of the last month.

Like the image below.

EOMONTH Function

If we break our problem,

            1.  We need the end date of the month or the last date of the month

            2.     The last month will be current month and the number of months provided.

            3.     Next step is to get the difference between the Start date and the ‘Month end Date’

In this scenario, EOMONTH function needs to be used.

 

Excel has a function which will give the last date of the month, supplied number of months.

 

The EOMONTH function is a built-in function in Excel which is a Date/Time Function.

 

Syntax :

 

The syntax for the EOMONTH function in Microsoft Excel is

 

=EOMONTH( start_date, month )

 

Parameters or Arguments

 

start_date : A valid date to return the day.

This function can work after the start date or before the start date.

Month : Count of months in number

Returns : The EOMONTH function returns a serial date value

Therefore, please remember to format the cell or range of cells as date.

In this above example, we need to type the formula in the C3 cell.

 

The formula is =EOMONTH(A3,B3).

 

EOMONTH Function

You can notice one month is given negative sign. It will calculate the date before the start date.

 

Thank you for reading...

Free Macro to Send emails with Attachments

Hello Friends,

No discussion on Excel today (!), rather I am giving you one FREE MACRO developed by me to send bulk mails which can be downloaded from the link below this article.

" Please ask for a copy of the macro in the comment box, I will send the link to download the macro free of costs. This is to protect the macro from some malicious cracker. "

"Please remember Cracking a Macro is Easy but making a Macro needs your Planning, Imagination, Knowledge and Skill..." . I will let you know hoe to make this step by step, but it will take time to master Excel like a Pro with compact knowledge.

    Auto Mailer Macro Free

Why I made this Excel Macro or Excel VBA Code ?

Recently I needed to send few mails with recipient’s data, I thought I could use Mail Merge to get the job done. Yes, it is done but “How to mark ‘CC’ ?”.

I didn’t find it. I Google it, nope, no help.

So, I thought I could use Excel Macro (VBA) to get the job done. Three hours it took to complete this macro using which I can send bulk email from excel with attachment.

After that I made some improvements and decided to provide this excel macro to send emails with or without attachments as FREE DOWNLOAD to you all because I understand the pain you feel sending emails one by one.

I have given you the option to send one universal attachment (Planning to give the option to attach different attachments for different recipient) in this excel macro.

One Request

Please DO NOT use this free macro to send spam mails. It is your responsibility how to use it.

Also, I am not responsible for any problem which may arise after sending mails. This macro is tested by me at least 100 times and I am getting 100 times positive result. Please check two or three mails before sending final mails.

This is a free excel macro to send emails automatically once the data is feed in the excel sheet, do not sale or do not purchase from anybody.

Please write to me in the comment box to improve this Excel Macro – Automatic Mails from Excel without using MS Word Mail Merge. I will add some more features to this FREE MACRO.

Please read this GUIDELINES carefully then enjoy your Office Work.

DOWNLOAD FREE email MACRO HERE   

v This is free version-1 macro to send emails using Outlook where you can’t send (?) mails in CC or BCC; unlike other Add-ins you don’t need to install

v As this is a macro, you need to ‘ENABLE’ macro clicking ‘Enable Macro’ in options below Formula bar or you can do this using File à Options à Enable macro.

v You will notice 25 columns of which one is ‘Identifier’ which is not being used in this macro (you can use it for your understanding) but append your recipient’s mail ids in TO, CC & BCC column header.

v Please note that do not place any special character including space(s) in the TO, CC, BCC column, leave it blank.

v After entering data in the first 3 or 4 columns you can enter data which you need to plot in the mails. You can use maximum ‘21’ field in the mail body as per this version.

v It is strongly recommended to use your Own Field / Column names replacing Data_1, Data_2, Data_3 etc., to post the required data in the Mail Body.

v After cross checking the data click on the button named Click Me for Show Control Panel.

v In the Auto Mailer system

Ø Enter No of e-mails to be sent

Ø Enter the Subject Line

Ø Start typing your mail in ‘Mail Body’ space or you can alternatively copy and paste from MS-Word (RECOMMENDED) or any document.

Ø Place your Cursor where you want to place your appropriate data, tick the data from ‘Insert Data’ data feeder.

Ø You can add one universal attachment in every mails.

Ø Please cross check and click on Send Button to start sending emails.

DOWNLOAD FREE EXCEL MACRO SEND BULK EMAILS

Please ask for a copy of the macro in the comment box, I will send the link to download the macro free of costs. This is to protect the macro from some malicious cracker.

Thank you… Keep visiting and commenting…

Excel Formula To Calculate Age in Years Months and Days On a Specific or Current Date

Hello Friends,

As promised in this article I will show you the process to calculate the exact age in Days, Months and Years from two given dates be it current date or any specific date.

I will use DATEDIF function to get the age in year, month & day.

Ifyou need to know about this function please click hear to know how thisfunction works.

Just to recall I would like to quote from my own article, "Please note, DATEDIF function is not like other functions, Excel will not help opening function argument box to be filled with data but if you provide all the data in correct order, it will help you a lot in future."

Syntax

The syntax for the DATEDIF function in Microsoft Excel is as below,

=DATEDIF( start_date, end_date, interval )

*** End date must be greater than Start date

In the below article you can notice that I have taken Date of Birth in 'B' column and I have taken current date in the 'C' column and the result is in the next column.

Calculate Age in Year Month and Day

Calculate Age in Year Month and Day - Formula

Calculate Age in Year Month and Day - Formula

To get exact age from a specific or current date we need to use the same formula and we need to use one function three times with CONCATE function or using "&" operator.

the formula is :

=DATEDIF(B4,C4,"Y") & " Years " & DATEDIF(B4,C4,"YM") & " Months " & DATEDIF(B4,C4,"MD") & " Days"

Note : I can modify this function using CONCATE function and then the excel formula becomes...

=CONCATENATE(DATEDIF(B4,C4,"Y"), " Years " & DATEDIF(B4,C4,"YM"), " Months " & DATEDIF(B4,C4,"MD"), " Days")

I use '&' to avoid extra function.

Now if we analyze the formula then we notice that the this formula is using DATEDIF function thrice but the last parameter is changing.

The meaning of these last parameters are as below.

Y       - The of Complete Year

YM   - The difference between the Months (Days and Years are ignored)

MD   -  The difference between the Days (Months and Years are ignored)

I hope it is quite easy way to calculate the exact age in years and months and days.

Keep reading and keep growing your knowledge in Excel...

Thank you for reading...

DATEDIF Function

Today I will discuss about DATEDIF function which is very helpful function when dealing with Dates. In next article we will use DATEDIF function to get the actual age on a given date as well as on current date. Therefore, this function is very important function.

DATEDIF function returns the difference between two date values, based on the interval specified.

Please note, DATEDIF function is not like other functions, Excel will not help opening function argument box to be filled with data but if you provide all the data in correct order, it will help you a lot in future.

Let us first go through the syntax of this function.

The DATEDIF function is categorized as a Date/Time Function.

Syntax

The syntax for the DATEDIF function in Microsoft Excel is as below,

=DATEDIF( start_date, end_date, interval )

*** End date must be greater than Start date

Parameters or Arguments

start_date and end_date

The two dates to calculate the difference between.

interval

The interval of time is used to calculate the difference between two dates. Below is a list of valid interval values.

How to use DAYDIF Function

Returns

The DATEDIF function returns a numeric value.

Example :

DATEDIF is very simple function, you need to put the proper arguments within this function and the result will be displayed.

I have given below few examples, please go through it.

How to use DAYDIF Function

Limitations :

1.   Microsoft Excel will not help you fill in the DATEDIF function like other functions using argument window.

2.   Microsoft recommends not using the "MD" value for unit because it "may result in a negative number, a zero, or an inaccurate result" But there are ways to overcome these errors which I will discuss in next article.

If you still have some questions, feel free to ask in the comment box.

I hope you have enjoyed this small article. Take care…

Thank you for reading….

RAND Function - Generate Random 'Decimal' Numbers In Excel

In my last article I discussed about how to generate RANDOM numbers in Microsoft Excel which is very easy using the function RANDBETWEEN. You type the function in excel and provide the Bottom and Top number (as lower and upper limit) and you get the Random numbers.

But if you notice carefully all these randomly generated numbers are integer type numbers, therefore, in this article I will discuss with you how to generate Decimal Random Numbers.

One solution is to divide the output of RANDBETWEEN numbers by 10, 100 or 1000, i.e., in multiple of 10.

But the problem is the integer part of these numbers will reduce and the number will not be within bottom and top range.

For example, suppose the output of RANDBETWEEN is 4387 and we divide it by 10, it will turn into 438.7, divide by 100 it will be 43.87, further divide by 1000, the output will be 4.387.

Which is not desirable.

The solution is to use RAND function.

Lets first discuss about the RAND Function.

Syntax

The syntax for the RAND function in Microsoft Excel is very simple.

=RAND( )

Parameters or Arguments

No parameters or arguments require for the RAND function.

Note : The Microsoft Excel RAND function returns a random number which is greater than or equal to 0 and less than 1. The RAND function returns a new random number each time your spreadsheet recalculates, similar to RANDBETWEEN function.

Example :

To use RAND function type =RAND() and press enter and you are done.

RAND Function

But did you noticed integer part is missing, why ?

Because RAND function output is from 0 to 1.

So, you can modify your formula by multiplying 10 and you may or may not get the decimal number having integer part.

RAND Function

This is because if the output of RAND function is starting with 0 then the number will be without having any integer part.

Also notice that we have no control over these Random numbers i.e., no upper or lower boundary or limit is implemented.

The Solution :

The solution is to modify the formula as below :

=RAND()*(B-A)+A

Where B represents Upper limit or Top number and A presents the Lower limit or bottom number.

RAND Function

What is the function of (B-A)+A in RAND formula ?

The Ordering of Mathematical Operation teaches us that multiplication is first to be calculated in this formula and then addition and then Subtraction will be applied.

Ordering of Mathematical Operation

The steps are

       

1.   RAND generates a random number between 0 to 1

2.   This number gets multiplied by B and A

3.   New numbers are subtracted

4.   then summed up by A

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...