Reshaping date format

[daregu91]

Hi everyone.

I would like to build a code that transforms my current format into a desired format for my dates. Both formats should be kept as General. Please find below some examples:

Current format: 1/1/2018 12:00:00 AM
Target format: All : [1/1/2018,2/1/2018)

Current format: 2/1/2018 12:00:00 AM
Target format: All : [2/1/2018,3/1/2018)

Current format: 10/1/2018 12:00:00 AM
Target format: All : [10/1/2018,11/1/2018)


In the target format I add some text, ignore the 12:00:00 AM part, and include in bracket current month and next month.

I would really appreciate your help to build this formula.

Thank you.

David

[Glenn Kennedy]

Try this:

="All: ["&TEXT(B3,"m/d/yyyy")&","&TEXT(EDATE(B3,1),"m/d/yyyy")&")"

differences between USA date formats and the way everyone else records dates makes it difficult for me to replicate your source data!!

[daregu91]

Hi Glenn,

It worked, thank you very much. I have been living in the US for 3 years and still have a hard time reading dates

David

[sandy666]

another way with PowerQuery
(it doesn't work without PowerQuery add-in)

[daregu91]

Hi all,

Thank you very much. I have another question, do you how I could build a formula that does the opposite thing? From All : [1/1/2018,2/1/2018) to 1/1/2018 12:00:00 AM.

Thank you again.

David

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[sandy666]

here is
as before, done with PowerQuery

[daregu91]

Thank you Sandy. I need to use this code inside a vlookup formula, do you know how I could see those steps you did with PowerQuery in a single Excel formula?

Thanks again.

David

[sandy666]

Here is Master of Formula, Glenn and probably he will give you a very good solution

[sandy666]

but you can try this one

Please Login or Register  to view this content.

and custom format d/m/yyyy hh:mm AM/PM

or this:

Please Login or Register  to view this content.

[daregu91]

Hi Sandy,

Thank you very much. I think there is an added difficulty; both in the source and the result, dates with single digits are not displayed with a zero in front, so it is 1/1/2018 and not 01/01/2018. That makes it more complex because you may have one and two digit months, and using the MID function gives you different results depending on that.

[sandy666]

so try this

Please Login or Register  to view this content.

or easier

Please Login or Register  to view this content.

because 8 is constant (in this case) which is the position of the first character of required string

All : [1/1/2018,2/1/2018) 7 + 1 ==> 8

[FlameRetired]

Similar to sandy's approach.

Please Login or Register  to view this content.

and then format accordingly. (m/d/yyyy h:mm AM/PM)

	D	E
3	All : [1/1/2018,2/1/2018)	1/1/2018 12:00 AM
4	All : [2/1/2018,3/1/2018)	2/1/2018 12:00 AM
5	All : [10/1/2018,11/1/2018)	10/1/2018 12:00 AM

[Phuocam]

Or try:

=--TRIM(MID(SUBSTITUTE(SUBSTITUTE(D3,"[",","),",",REPT(" ",100)),100,100))