How do i fixed the second to 00 using an excel formula?

[lmvelasco]

Good day, My Name is Luis and I new here. I find this forum as useful. I just want to ask something, Is there any formula that will fixed the seconds into 00.

Here's my sample format:

7/2/2021 10:01:45 PM and I want to to change it to: 7/2/2021 10:01:00 PM

any tip or formula on how to fixed the seconds value into zero?

Thank you in advance.

[Pete_UK]

You want to set them to zero, irrespective of the value (i.e. not round them up or down)?

Pete

[Pete_UK]

Ah well, if your date time is in A1 you could use this in B1:

=INT(A1)+TIME(HOUR(A1),MINUTE(A1),0)

Copy down as required.

Hope this helps.

Pete

[lmvelasco]

It works...


Thank you so much sir pete_uk!!!!

[lmvelasco]

how do I close or tagged my post as solved?

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[lmvelasco]

done.. again thank you so much! more power!!!

[braxtonleo]

ello experts,

I'm a noob here and need help in this

I'm actually working on a tracker that has a cell to calculate the turnaround time (tat) which only shows the hours spent within the staff working hours.

Given 2 dates:
A2: Start date = 1/12/12 10:00
B2: End date = 1/16/12 15:00

Shift start time: 8:00 AM
Shift end time: 5:00 PM

Using this formula:
=IF(DAY(B2)-DAY(A2)=0,MIN("17:00",(B2-INT(B2)))-MAX("08:00",(A2-INT(A2))),(MIN("17:00",(B2-INT(B2)))-"08:00")+("17:00"-MAX("08:00",(A2-INT(A2))))+IF(DAY(B2)-DAY(A2)>1,(DAY(B2)-DAY(A2)-1)*(9/24),0))

Result:
In "[h]:mm" format = 41:00
I need the result to be in this format "x day(s) xx hour(s) and xx min" but when I format it, gave me "1 day(s) 17 hour(s) and 00 min".

As you can see, in normal calculation of shift start at 8AM, stop at 5PM. the time difference is 9 hours. And the result above should return "2 day(s) 08 hour(s) and 00 min".

Any idea on how to fix this? And these results should also able to be sum up or average out in my report. Any help is greatly appreciated.

Thank you.

[Pete_UK]

You should start your own new thread, rather than try to hijack someone else's (see Forum Rule 04 at the top of the screen).

Pete