SOLVED

Conference Schedule - is this possible?

%3CLINGO-SUB%20id%3D%22lingo-sub-1444334%22%20slang%3D%22en-US%22%3EConference%20Schedule%20-%20is%20this%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444334%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20for%20an%20upcoming%20conference%3A%20a%20begin%20date%2Ftime%20and%20an%20end%20date%2Ftime%20using%20this%20formula%3A%26nbsp%3Bm%2Fd%2Fyyyy%20h%3Amm%20AM%2FPM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E06%2F27%2F2020%208%3A15%20AM%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2006%2F27%2F2020%2010%3A00%20AM%3C%2FP%3E%3CP%3E06%2F27%2F2020%2010%3A15%20AM%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2006%2F27%2F2020%2012%3A00%20PM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20to%20combine%20the%20two%20columns%20into%20one%26nbsp%3B%3CSTRONG%3EDate%2FTime%26nbsp%3B%3C%2FSTRONG%3Ecolumn%20so%20it%20will%20appear%20as%20follows%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESaturday%2C%2006%2F27%2F2020%2C%208%3A15%20AM%20-%2010%3A15%20AM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1444334%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444381%22%20slang%3D%22en-US%22%3ERe%3A%20Conference%20Schedule%20-%20is%20this%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690694%22%20target%3D%22_blank%22%3E%40Nikolette%3C%2FA%3E%26nbsp%3Bhi%20i%20think%20if%20you%20follow%20this%20link%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fofficearticlehelp.live%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fofficearticlehelp.live%2F%3C%2FA%3E%26nbsp%3Bit%20will%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444622%22%20slang%3D%22en-US%22%3ERe%3A%20Conference%20Schedule%20-%20is%20this%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690694%22%20target%3D%22_blank%22%3E%40Nikolette%3C%2FA%3E%26nbsp%3BTry%20this%20formula%20in%20C2%20and%20copy%20it%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXT(A2%2C%22dddd%2C%20m-d-yyyy%20h%3Amm%20AM%2FPM%22)%26amp%3B%22%20-%20%22%26amp%3BTEXT(MOD(B2%2C1)%2C%22h%3Amm%20AM%2FPM%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20will%20work%20if%20both%20start%20and%20end%20are%20on%20the%20same%20day%2C%20exactly%20as%20you%20indicated%20in%20your%20examples.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20part%20creates%20a%20text%20of%20the%20start%20date%20and%20formats%20it%20the%20way%20you%20want%20and%20then%20appends%20a%20%22%20-%20%22%20and%20a%20text%20for%20the%20the%20time%20component%20of%20the%20end%20date.%20Note%20that%20Excel%20stores%20dates%20and%20times%20as%20numbers.%20The%2027th%20of%20June%202020%20at%2010%20AM%20%3D%26nbsp%3B44009.4166666667%2C%20representing%2044009%20days%20from%20January%201%2C%201900%20plus%200.4166666667%20of%20one%20day%20(i.e.%2010%20hours).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444635%22%20slang%3D%22en-US%22%3ERe%3A%20Conference%20Schedule%20-%20is%20this%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444635%22%20slang%3D%22en-US%22%3EBe%20aware%20The%20link%20is%20phishing!%20Reported%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444637%22%20slang%3D%22en-US%22%3ERe%3A%20Conference%20Schedule%20-%20is%20this%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20so%20very%20much%2C%20this%20is%20perfect.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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!

 

 

2 Replies
Highlighted
Best Response confirmed by Nikolette (New Contributor)
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).

 

Highlighted

@Riny_van_Eekelen Thank you so very much, this is perfect.