What's the formula to add hours and minutes in Excel?

Question

I'm using Excel. A1=14:20pm, B1=9:45am. I would like to know what would be the formula to put in cell C1 to get the answer 19 hrs 25 min. (It has to be shown as 19:25). I've been trying " =sum(b1-a1)" but that's not correct and that's the extent of my formula writing knowledge. I could really use the help!

Answer 1

You need to include the actual dates in the cells when you enter the data. Excel displays negative time values as ############. To avoid this you need the dates in the value.

The dates don't need to show though. Just choose a format for the cells that show the times only. The format that will give you 19:25 is h:mm.

Oh yeah, the formula would just be =B1-A1.

Answer 2

If no one can help you here then I highly recommend mrexcel.com, the people on that forum are superb.

I don't quite understand the question. So is the goal that the outcome look like this in one cell?
"19 hrs 25 min" or would you like that in two cells split up?

Answer 3

enter your value required to be converted in cellular A1 (formatted as text cloth or enter as such '2:40 seven) In cellular B1 enter here (all one line pls as YA breaks formulation) =value(LEFT(A1,seek(":",A1) -a million)*60+(MID(A1,seek(":",A1) +a million,2))) answer comes back in seconds (excuse the pun). Will convert any style of minutes wisely yet no longer if entered as hours, minutes and seconds. HTH

Answer 4

It is in the same day, right?
If so, use this function:
=LEFT( TEXT( A1- B1,"hh:mm"), SEARCH( ":", TEXT( A1- B1,"hh:mm" ),1) -1) + ( VALUE( MID( TEXT( A1- B1,"hh:mm" ),SEARCH( ":", TEXT( A1- B1 ,"hh:mm"),1)+1 ,2)))/ 60

It is complicated, but it is working as you asked, I wrote this function long time ago, I think I can enhance it now

Any way
If you have it in differant dates, or have more questions,
mail me here

Read my profile, I am the XLMan

Answer 5

just right click the cell and click format cells then go to time. and set the time.