1

I'm Trying to display dates in a calculated column in the format of "DD - DD Month Year" (05-10 August 2018) or as "DD Month - DD Month Year" (30 August - 03 September 2018) if the Months are different.

I know the formula for both.

first is:

=CONCATENATE(text([Start Time],"dd"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy"))

Sencond is:

=CONCATENATE(text([Start Time],"dd"), " ", text([Start Time], "mmmm"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy"))

I tried making an IF statement but i cant get it to work.

=IF(Month[Start Time] = Month[End Time], =CONCATENATE(text([Start Time],"dd"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy")), =CONCATENATE(text([Start Time],"dd"), " ", text([Start Time], "mmmm"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy")))

Please advise how show the dates in specified format?

  • Hi @JinBrau, don't forget to accept the helpful answer as the best answer. It will also help others in the forum to get the answer quickly. – JoannaW_MSFT Dec 19 '18 at 1:20
1

I think the main issue in the true and false section! where you added additional equal = like =CONCATENATE

Try to remove equal = before CONCATENATE and use the below formula that should be working properly

Formula

=IF(Month([Start Time]) = Month([End Time]), CONCATENATE(text([Start Time],"dd"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy")), CONCATENATE(text([Start Time],"dd"), " ", text([Start Time], "mmmm"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy")))
0

Need "()" in MONTH([column]) function.

Summary:

IF(MONTH([Start Time]) = MONTH([End Time]), CONCATENATE(text([Start Time],"dd"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy")), CONCATENATE(text([Start Time],"dd"), " ", text([Start Time], "mmmm"),"-", text([End Time], "DD"), " ", text([End Time], "mmmm"), " ", text([End Time], "yyyy")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.