Solved excel, if its above 2hrs then result should be red.

December 13, 2012 at 16:51:57
Specs: Windows 7
hoping someone can help.

i need a formula which will read the date in say, A1 & B2 and give a result if its taken longer than 2 hours to resolve a case.

A1: 14/12/2012 12:00pm
B1: 15/12/2012 13:00pm

so, in C3, the formula should read it took 1 day and 1 hr to complete this case....


See More: excel, if its above 2hrs then result should be red.

Report •

December 13, 2012 at 17:08:00
✔ Best Answer
Hi KK88

First you would need to be sure that your dates in A1 and B1 are in Date format. As you have them in your post they would not be correct - you would need to have a space before the PM or AM.

Once that's done you can have this formula to give your result

=INT(B1-A1) & " Day(s) " & ROUND(((B1-A1)-INT(B1-A1))*24,1) & " Hours"

this will give you

1 Day(s) 1 Hours

as the result in cell C3

If you just have =B1-A1 (much simpler) this will give you 1.04 which is 1.04 days. without all the fancy stuff.

Report •

December 16, 2012 at 14:40:34
hi AlteK - thank you so much for your help. the formula you gave me worked exactly how i wanted it. Thank you again, really appreciate your assitance.
Merry Christmas :)

Report •

December 16, 2012 at 14:42:04
No worries - and happy holidays to you too.

Report •

Related Solutions

December 16, 2012 at 18:16:16
Hi AlteK,

was wondering if you could me out again.

in reference to the the formula you helped with above..

i want to now use a formula which will look through the outcome as a result of the above formula and tell me where its taken longer than 2.5hrs....

is this possible?

Thank you.

Report •

December 16, 2012 at 18:34:23
The best ways to do that is with Conditional Formatting. Assuning you have Excel 2007 or 2010 you would

Highlight all the cells that will contain your results - lets assume they are in C1 to C100
Click on Conditional Formatting on the Home ribbon
Click on New Rule
Click on Use a Formula to determine which cells to format
In the "Format Values are type in

=$C1>2.5/24 if you used the simple option I gave
=$B1-$C1>2.5/24 if you used the wordy option

Then click the Format button and select how you want to highlight the value over 2.5 (usually "Fill" with a shade)

Click okay and with any luck you should see the values you want shaded. If not, you'll need to post a sample of your data so we can see what the precise settings should be..

Report •

December 20, 2012 at 17:54:27
Hi AlteK - you;ve done it again, thank you so much for giving me the must be an excel guru!

thank you and merry xmas :)

Report •

Ask Question