Thread: How can one zero out or drop the seconds in a time format?

Hello - 1st off, I just want to say that this forum has been a great source of information when dealing with excel help - GREAT JOB.

I have been having an issue I just can not seem to get to work correctly. I am not even sure if I am going about it the right way or if I am just missing one little detail.

Over all picture, I am trying to create a time sheet that will automatically import the time stamp activity: Shift Start, Lunch Start, Lunch End, & Shift End from an updated pivot table spreadsheet. The information in the pivot table is text only data.

I have been able to figure out how to import the time stamp from the pivot table + converting from text to time format. The problem I am having is, I want to drop the seconds off the time so it is just hh:mm. I have tried changing the cell format to time hh:mm format, but when I start doing the calculations/converting to decimal to figure out how many hours worked, it still figuring the seconds (that are not showing in hh:mm cell format).

The sample I uploaded contains the time sheet I have created with a small sample of the pivot table.

fyi: the pivot table is structured so only the design can be changed, but none of the data.

Basically I just want to the hours and minutes to be either imported over or have the seconds dropped/zeroed out before or during the conversion/calculations.

Can someone please help me out and see if there is anyway this can be achieved?

I am sure you guys can figure it out in 5 minutes or less. I am know just enough to know I do not know enough about excel

Thank You

Hi,

In E6 and copied where required, try this

=IF((OR(D6="",D5="")),0,IF((D6<D5),((TIME(HOUR(D6),MINUTE(D6),0)-TIME(HOUR(D5),MINUTE(D5),0))*24)+24,(TIME(HOUR(D6),MINUTE(D6),0)-TIME(HOUR(D5),MINUTE(D5),0))*24))