# 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. e.g 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....help!

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

#1
December 13, 2012 at 17:08:00
 Hi KK88First 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 you1 Day(s) 1 Hoursas the result in cell C3If you just have =B1-A1 (much simpler) this will give you 1.04 which is 1.04 days. without all the fancy stuff.

Report •

#2
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 •

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

Report •

Related Solutions

#4
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 •

#5
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 wouldHighlight all the cells that will contain your results - lets assume they are in C1 to C100Click on Conditional Formatting on the Home ribbonClick on New RuleClick on Use a Formula to determine which cells to formatIn the "Format Values are type in=\$C1>2.5/24 if you used the simple option I gaveor=\$B1-\$C1>2.5/24 if you used the wordy optionThen 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 •

#6
December 20, 2012 at 17:54:27
 Hi AlteK - you;ve done it again, thank you so much for giving me the formula...you must be an excel guru! thank you and merry xmas :)

Report •