Question description:
This user has given permission to use the problem statement for this
blog.
i need a formula for calculating two dates differance month and if day exceds 15 days it should calculate a one month
Solved by G. J. in 25 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/08/2018 - 01:42
Welcome to ExcelChat!
User
16/08/2018 - 01:43
hai
Excelchat Expert
16/08/2018 - 01:43
So you need a formula that returns the difference in months between two dates, right?
User
16/08/2018 - 01:44
ya correct and i need if the differance between is one month 16 days it should calculate as 2 months
Excelchat Expert
16/08/2018 - 01:45
what if the difference is just 14 days, for example? Should it return 0 month?
User
16/08/2018 - 01:46
ya if it is 1 month 14 days it can be calculate as 1 month
User
16/08/2018 - 01:49
what happend
Excelchat Expert
16/08/2018 - 01:49
What do you mean?
Excelchat Expert
16/08/2018 - 01:50
I haven't finished doing the solution yet, I will write you back once I finish
User
16/08/2018 - 01:50
ya sure thanks
Excelchat Expert
16/08/2018 - 01:54
Okay, I have finished the formula
Excelchat Expert
16/08/2018 - 01:54
=DATEDIF(A2,B2,"M")+if(mod(B2-A2,30)>=15,1,0)
Excelchat Expert
16/08/2018 - 01:54
first we count the real amount of months between the two dates
Excelchat Expert
16/08/2018 - 01:54
then we take a look at the remained days
Excelchat Expert
16/08/2018 - 01:54
If we have 15 or more days left, then we add 1 more month
Excelchat Expert
16/08/2018 - 01:55
if we have less than 15, we don't add anything
User
16/08/2018 - 01:56
o nice you are great i will try it right now
Excelchat Expert
16/08/2018 - 01:56
Yes, please give it a try
Excelchat Expert
16/08/2018 - 02:00
We need to change the format of the date
Excelchat Expert
16/08/2018 - 02:00
because it varies depending on your system language
Excelchat Expert
16/08/2018 - 02:00
On this sheet these dates are not recognised by Google Sheets
Excelchat Expert
16/08/2018 - 02:01
On this Sheet we have US format of dates, but it doesn't matter, when you download the file, you will use your date format, and formula will work
User
16/08/2018 - 02:02
o thankyou so much
User
16/08/2018 - 02:03
i was trying from morning 11 o clock you saved my day thankyou so much
Excelchat Expert
16/08/2018 - 02:03
You are welcome
Excelchat Expert
16/08/2018 - 02:03
Please don't forget to save the file
Excelchat Expert
16/08/2018 - 02:03
File -> Download as -> xlsx
User
16/08/2018 - 02:04
ya i have downloaded thank you
Excelchat Expert
16/08/2018 - 02:04
You are welcome, wish you a great day
User
16/08/2018 - 02:04
you are so fast in formula calculation
Excelchat Expert
16/08/2018 - 02:05
That is my main job, and a lot of other guys like me work here
Excelchat Expert
16/08/2018 - 02:05
So in case of any questions related to Excel / Google Sheets, this service allows to save a lot of your time
User
16/08/2018 - 02:06
how can i get to chat you on next time
Excelchat Expert
16/08/2018 - 02:07
You can't choose which expert will help you.. but I work here quite often. Beside that, I am sure you will love another experts as well
User
16/08/2018 - 02:07
thankyou so much this is the great website i ever searched thanks a lot
Excelchat Expert
16/08/2018 - 02:08
You are welcome!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
