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:00pmso, in C3, the formula should read it took 1 day and 1 hr to complete this case....

help!

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.

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 :)

No worries - and happy holidays to you too.

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.

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

or

=$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..

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 :)

Ask Your Question

Weekly Poll