Question description:
This user has given permission to use the problem statement for this
blog.
I need a automatic formula that I could use to change or "push" dates forward, for example, if I have a date of 18.06.2018 and I would like to change it to 01.07.2018. The dates are obviously not always the same so it would take me very much time to do all manually and that's why I would need a formula, can you help me with this?
Solved by S. Q. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
19/06/2018 - 07:24
Hi
Excelchat Expert
19/06/2018 - 07:24
Hello
Excelchat Expert
19/06/2018 - 07:24
How are you today?
User
19/06/2018 - 07:25
I'm fine thanks, how about you?'
Excelchat Expert
19/06/2018 - 07:25
I am great, thank you!
Excelchat Expert
19/06/2018 - 07:25
As I read, you want to push dates forward.
User
19/06/2018 - 07:25
yes!
Excelchat Expert
19/06/2018 - 07:26
so would there be any criteria to push it forward?
User
19/06/2018 - 07:26
yes the dates always need to be pushed forward to the first date of the next month
Excelchat Expert
19/06/2018 - 07:26
Like any condition you want to relate it to? and if you can share the file, it would be great .
User
19/06/2018 - 07:27
so for example if the date would be 18.06 it should be pushed forward to 01.07
Excelchat Expert
19/06/2018 - 07:27
Ok.
User
19/06/2018 - 07:27
sorry I can't share the file since it's company policy
Excelchat Expert
19/06/2018 - 07:27
No problem, I understand.
Excelchat Expert
19/06/2018 - 07:27
so let's say, your date is in Column B
Excelchat Expert
19/06/2018 - 07:27
so the result would be in Column C
User
19/06/2018 - 07:28
yes
Excelchat Expert
19/06/2018 - 07:28
Ok
Excelchat Expert
19/06/2018 - 07:28
t me send you an excel file with the working formula.
User
19/06/2018 - 07:29
sounds good, are you sending it to my email?
Excelchat Expert
19/06/2018 - 07:29
Here only
User
19/06/2018 - 07:29
okay
Excelchat Expert
19/06/2018 - 07:29
Once it is complete, I will attach it here.
Excelchat Expert
19/06/2018 - 07:29
Give me few minutes
Excelchat Expert
19/06/2018 - 07:32
Here it is
[Uploaded an Excel file]
Excelchat Expert
19/06/2018 - 07:33
the formula used is =eomonth(B2,0)+1
User
19/06/2018 - 07:33
thank you looks great
User
19/06/2018 - 07:33
could you quickly explaine how it works
Excelchat Expert
19/06/2018 - 07:33
Great!
Excelchat Expert
19/06/2018 - 07:34
=eomonth(B2,0) will give you the last date of the month in Cell B2
Excelchat Expert
19/06/2018 - 07:34
and then I am adding 1 to it to show it the next date i.e. the first date of the next month
Excelchat Expert
19/06/2018 - 07:34
it becomes =eomonth(B2,0)+1
User
19/06/2018 - 07:34
so if I would like it to be 01.01.2018
User
19/06/2018 - 07:34
what would I put in then?
Excelchat Expert
19/06/2018 - 07:35
any date in december
Excelchat Expert
19/06/2018 - 07:35
any date in December 2017
User
19/06/2018 - 07:35
sorry in this case I meant 01.01.2019
Excelchat Expert
19/06/2018 - 07:36
Can you see the preview on the right
User
19/06/2018 - 07:36
oh sorry this is american dates
Excelchat Expert
19/06/2018 - 07:36
the format I have sent is mm/dd/yyyy
User
19/06/2018 - 07:36
yes I got it, sorry
Excelchat Expert
19/06/2018 - 07:36
you want in mm.dd.yyyy
User
19/06/2018 - 07:37
dd.mm.yyyy
Excelchat Expert
19/06/2018 - 07:37
I can change the format as per your requirement
Excelchat Expert
19/06/2018 - 07:37
Ok
Excelchat Expert
19/06/2018 - 07:37
give me a min
Excelchat Expert
19/06/2018 - 07:38
Here it is
[Uploaded an Excel file]
Excelchat Expert
19/06/2018 - 07:38
so if you will put month as Feb or 02 it will show the first date of March
Excelchat Expert
19/06/2018 - 07:39
so it will always show the first date of next month.
User
19/06/2018 - 07:39
okay I think this will work!
Excelchat Expert
19/06/2018 - 07:39
Great!
Excelchat Expert
19/06/2018 - 07:40
Please do provide a good review and feedback, if you are satisfied with my solution.
User
19/06/2018 - 07:40
tank you very much! I will for sure remember this site
User
19/06/2018 - 07:40
will do!
Excelchat Expert
19/06/2018 - 07:40
Great! Thank you for your time
Excelchat Expert
19/06/2018 - 07:40
great day ahead!
User
19/06/2018 - 07:40
same, by!
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.
