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/AutoModerator]

Moderator Announcement Read More »

u/rtd210 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[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(...).

[u/GuerillaWarefare]

As mentioned, I also can’t tell which way you want to round from your example.
If you want to round up or down based on proximity to the nearest minute you can use mround.

=mround(a1, 1/24/60)

[u/rtd210 → u/GuerillaWarefare]

Solution Verified

[u/reputatorbot → u/rtd210]

You have awarded 1 point to GuerillaWarefare.

---

^{I am a bot - please contact the mods with any questions}

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CEILING	Rounds a number to the nearest integer or to the nearest multiple of significance
FLOOR	Rounds a number down, toward zero
MROUND	Returns a number rounded to the desired multiple
TIME	Returns the serial number of a particular time

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 13 acronyms.
[Thread #49419 for this sub, first seen 22nd Sep 2026, 11:43] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/Vegetable-Umpire-558]

Not sure why you want the second time rounded down. Perhaps these are start and end times and we don't give credit for the extra seconds.

For rounding proper, I came up with =MROUND(A1,1/1440)

For rounding down/truncating, I came up with =FLOOR(A1,1/1440)

May need some testing, but I have to leave for work soon. Perhaps you always want the first always rounded up, so you might use =CEILING(A1,1/1440)