Break Apart Calendar Event Date/Time

So I’ve connected a Google Calendar to my doc and it pulls everything across I need. However, I don’t like the way it shows on the phone so I’m looking to see how I can improve this. What happens now is it shows the appointment like this:

You can see the date and time all bunched together. What I would like it to show is :

Sep 17, 6:30-8:00PM

Is that possible? My thought would be to break up the start date/time and end date/time, and then build it back together in a new column? Or am I overthinking it?

Hi @Chad_Oglesbay,

Will you be able to share a dummy copy of your doc, where at least the above is available.
Formulas is not my strongest point, but I will try to give it a chance, otherwise for sure there will be somebody in the community to support on this.

When I see people doing this, I get inspired:

Hello, here is a copy of my doc with the calendar: Pack Calendar Doc Your example above is really cool! I would love to be able to make mine like that!

Credits to @anon86103599

For your kind reference;

Dear Chad,

)* Not clear to me why I get only one digit minutes

)** Not clear to me how to change between 18 and 06 - AM / PM

Hopeful it give some inspiration and I am sure there are more elegant ways, hopeful we get more input from the community

Depending on how Start and End are formatted (Datetime, Date & Time), I’ve come with various possibilities for this :wink:

@Jean_Pierre_Traets : You’ll even find how to get from 18:30 to 6:30 PM :wink: .
I’ve also run into the problem of Minute() showing only one digit when Minute()=0

EDIT 2019-09-23T16:20:00Z : I forgot something :sweat_smile: : This is the correct formula (for other possibilities, see the doc below :wink: )

This is the formula of the blue result in the doc :wink:

Concatenate(MonthName([Start Date/Time],"MMM")," ",[Start Date/Time].Day(),", ",If(ToTime([Start Date/Time])<ToTime("12:00PM"),[Start Date/Time].Hour(),[Start Date/Time].Hour()-12),":",If([Start Date/Time].Minute()="0","00",[Start Date/Time].Minute()),"-",If(ToTime([End Date/Time])<ToTime("12:00PM"),[End Date/Time].Hour(),[End Date/Time].Hour()-12),":",If([End Date/Time].Minute()="0","00",[End Date/Time].Minute()),[End AM/PM])

Hope this help @Chad_Oglesbay :wink:

1 Like

Dear @Pch,

Thanks a lot for the detailed explanation :trophy:
An :eyes: opener for me to use an “if statement” to solve “0” to “00” :bulb:

1 Like

You’re welcome :grin: !

Dates formulas are often a little bit tricky :wink: !

But I shared the doc a little bit to early :sweat: as I didn’t think about events occurring “AM” to “PM” on the same day :sweat_smile:

So I just corrected that in the doc above :arrow_up: and the formula is :

Concatenate( MonthName([Start Date/Time],"MMM")," ",[Start Date/Time].Day(),", ",If(ToTime([Start Date/Time])<ToTime("12:00PM"),[Start Date/Time].Hour(),[Start Date/Time].Hour()-12),":",If([Start Date/Time].Minute()="0","00",[Start Date/Time].Minute()),If(Totime([Start Date/Time])>=ToTime("12:00 PM") AND ToTime([End Date/Time])>=ToTime("12:00 PM"),"-",If(ToTime([Start Date/Time])<ToTime("12:00 PM") AND ToTime([End Date/Time])<ToTime("12:00 PM"),"-","AM-")),If(ToTime([End Date/Time])<ToTime("12:00PM"),[End Date/Time].Hour(),[End Date/Time].Hour()-12),":",If([End Date/Time].Minute()="0","00",[End Date/Time].Minute()),If(ToTime([Start Date/Time])<ToTime("12:00 PM") AND ToTime([End Date/Time])<ToTime("12:00 PM"),"AM","PM") )

It’ll switch and concatenate AM or PM or AM and PM if needed :grin:

Saying this : Switch() or Switchif() might be other ways to get to that result too :wink:

Edit : I’m not sure thought that it will shows the concatenated start/end time on mobile and therefore solve the problem on mobile :thinking:. But who knows ? :wink:

1 Like