Jun 05 2020 05:45 PM
I have two columns for an upcoming conference: a begin date/time and an end date/time using this formula: m/d/yyyy h:mm AM/PM
06/27/2020 8:15 AM 06/27/2020 10:00 AM
06/27/2020 10:15 AM 06/27/2020 12:00 PM
Is there a formula to combine the two columns into one Date/Time column so it will appear as follows?
Saturday, 06/27/2020, 8:15 AM - 10:15 AM
Thank you!
Jun 05 2020 11:06 PM
Solution@Nikolette Try this formula in C2 and copy it down:
=TEXT(A2,"dddd, m-d-yyyy h:mm AM/PM")&" - "&TEXT(MOD(B2,1),"h:mm AM/PM")
This will work if both start and end are on the same day, exactly as you indicated in your examples.
The first part creates a text of the start date and formats it the way you want and then appends a " - " and a text for the the time component of the end date. Note that Excel stores dates and times as numbers. The 27th of June 2020 at 10 AM = 44009.4166666667, representing 44009 days from January 1, 1900 plus 0.4166666667 of one day (i.e. 10 hours).
Jun 05 2020 11:16 PM
@Riny_van_Eekelen Thank you so very much, this is perfect.
Jun 05 2020 11:06 PM
Solution@Nikolette Try this formula in C2 and copy it down:
=TEXT(A2,"dddd, m-d-yyyy h:mm AM/PM")&" - "&TEXT(MOD(B2,1),"h:mm AM/PM")
This will work if both start and end are on the same day, exactly as you indicated in your examples.
The first part creates a text of the start date and formats it the way you want and then appends a " - " and a text for the the time component of the end date. Note that Excel stores dates and times as numbers. The 27th of June 2020 at 10 AM = 44009.4166666667, representing 44009 days from January 1, 1900 plus 0.4166666667 of one day (i.e. 10 hours).