新聞財經娛樂♬資訊 News Economy

Hello Experts,

Please see attached file.

In my master file, I have a column that has time and date/time values.

IE:

3:34:25 AM
5:03:11 PM
1/2/1900 10:24:19 AM

I am looking for a formula, that will look at those values - whether it's just a time or date & time value - and make ALL the seconds show as "00".

Based on the values above,... I would like to see these as the answers.

3:34:00
17:03:00
58:24:00

I had first attempted to use the =FLOOR function, IE : =FLOOR(D433,1/1440)

The majority of the time, that formula would change all seconds to "00".  But I also noticed that on 'some' times, even if the time already was at "00" seconds, it would then subtract a minute off of the time.  So once I seen that happening, I stopped using the floor function.

I then moved on to using this formula - IE =TIME(HOUR(B4),MINUTE(B4),SECOND("00"))

Which was great, because it forced all times to have "00" seconds.  But I noticed those time values would never exceed 24 hours.

So basically it all boils down to this...

I am hoping to get a FORMULA (NOT CELL FORMATTING) that will change all seconds to "00" and allow for the number to exceed 24 hours.

Incase you ask, I'm NOT looking for the seconds to be "ROUNDED" one way or another.  Regardless of what the seconds are, they should all be "00".

I hope this all made sense, I am sooooooo frusted that I wasn't able to figure this out tonight.  I've spent hours on this so far troubleshooting... :::sigh:::  Help, please help!  lol

Thanks,

~ Geekamo

Hello,

you almost had it with your own formula in cell B11. Just add the number of days from the source date, like

=DAY(B4)+TIME(HOUR(B4),MINUTE(B4),0)

or starting with the original cell value in B3 works as well.

=DAY(B3)+TIME(HOUR(B3),MINUTE(B3),0)

Keep the cell formatting at [h]:mm:ss. The value is still a true time value, and not text (like the suggestions above), so you can still use it in calculations.

cheers, teylyn