HELP! Formula hours:minutes

Microsoft Excel 2003 (full product)
January 14, 2011 at 14:05:56
Specs: Windows XP

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!

See More: HELP! Formula hours:minutes

Report •

January 16, 2011 at 19:55:00
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 minutes
for 30:08 because 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.

Report •

January 17, 2011 at 07:12:50
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!

Report •

January 17, 2011 at 08:28:48
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.

Report •

Related Solutions

January 17, 2011 at 08:46:56
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.


Report •

January 17, 2011 at 15:28:40
You are welcome.

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

Report •

Ask Question