Microsoft Excel 2003 (full product)

Hi, I currently have a formula that calculates hours:minutes. I need the answer to be specific. My current answer comes out 30:08, but I need it to specifically say 30 hours and 08 minutes in the cell. Is there a way to do that?

My current formula is =D2+(A2>D2)-A2

Would really appreciate any help!

Thank you!

Joanna

Could you please post an example of the data that gets you a result of 30:08 and maybe a few more examples and their results? My first suggestion would be the following formula, but it returns

6 hours 8 minutesfor30:08because 30 is 1 day, 6 hours:=HOUR(A1) & " hours " & MINUTE(A1) & " minutes"

This works for 30:08, but I don't know if it will work for every result that you might get since I don't know what your data looks like.

=DAY(A1)*24 + HOUR(A1) & " hours " & MINUTE(A1) & " minutes"

Maybe if I knew exactly what data your formula was working on, I could refine one or the other to work in all cases.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Hi and thank you for replying. Data is as follows: Date/Time Received Image: Date Time Dr.:Signed Total Time

Cell A Cell D Cell E

12/1/2010 6:40 12/2/2010 12:48 30 hours and 8 minutes (30:08)I have data in between, but it does not have anything to do with the formula.

Do, my formula works to get teh 30:08 answer, but I wanted it to actually say 30 hours and 8 minutes so there is no confusion from the end reader (even though my header says HRS:MIN).

Thanks so much!

Before posting anymore data, please read the How To referenced in my signature line. This will explain to line up your data so that it ends up in the correct columns. Since there is no such thing as Cell A, Cell D, etc. I'll assume you mean Column A, Column D, etc.

Try this formula:

=DAY(D2+(A2>D2)-A2)*24 + HOUR(D2+(A2>D2)-A2) & " Hours " & MINUTE(D2+(A2>D2)-A2) & " Minutes"

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Your assumption is correct...Column A vs Cell A. Formula seems to be working exactly how I need it to!!!

Thank you very much for your time and expertise.

Sincerely,

Joanna

Ask Your Question

Weekly Poll