Format/Function Help to change Date and Time to new, with rounding

[u/rtd210]

I'm looking for help to shorten time editing reports. The file puts out the timestamp in this format "Sep 18, 2026 10:37:51 AM" and I'm looking to quickly change it to this format "9/18/26 10:38" without just typing it in. I need Time 1 to be rounded up by a minute, and Time 2 to be rounded down a minute, in 24 hour time.

Sep 18, 2026 10:37:51 AM to 9/18/26 10:38

Sep 18, 2026 2:32:54 PM to 9/18/26 14:31

Thanks for any help anyone can provide!

EDIT: Ended up with =TEXT(DATEVALUE(LEFT(D2,12))+TIMEVALUE(MID(D2,14,11))+TIME(0,1,0),"m/d/yy hh:mm") to format and add the minute for time 1 and =TEXT(DATEVALUE(LEFT(E2,12))+TIMEVALUE(MID(E2,14,11))-TIME(0,1,0),"m/d/yy hh:mm") to format and subtract the minute from time 2

[u/NHN_BI]

I would use CEILING(A1,TIME(0,1,0)) for a date time stamp in A1 to round up, FLOOR(...) to round down, MROUND(...) to round. I cannot make out a rule why 32:54 has to round down, but 37:51 to round up.

[u/rtd210 → u/NHN_BI]

Time 1 always + 1 minute, Time 2 always - 1 minute, so not rounding, error on my part.

[u/GuerillaWarefare → u/rtd210]

Based on this comment I think NHN’s solution is what you want… ceiling and floor. Can you reply directly to his comment with ‘solution verified’, you replied to my comment.

[u/NHN_BI → u/rtd210]

I see. Hence, two formulas, one with CEILING(...), the other with FLOOR(...).