Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and spot variances of cell values with a quick glance. Continue reading
Comments page 3. Total comments: 152
Hi,
I'm trying use a formula for working time calculation
IN OUT TT status
9:30 AM 6:00 PM 8:30
9:30 AM 9:00 PM 11:30
9:30 AM 12:00 AM 14:30
9:30 AM 3:00 AM 17:30
9:30 AM 5:00 AM 19:30
in states Colum i want if
9:30 AM 6:00 PM 1
9:30 AM 9:00 PM 1.5
9:30 AM 12:00 AM 2
9:30 AM 3:00 AM 2.5
9:30 AM 5:00 AM 3
what kind of formula i should use for this
Hello Zabiulla,
You can use Anant's solution, or one of the following two options:
1) Create a helper column and enter the following formula:
=IF(B10<A10,(B10+1-A10)*24,(B10-A10)*24)
It will let you get numeric values for the status. Then you can insert a simple IF formula with all combinations of the conditions and copy it down a new column:
=IF(D10=8.5,1,IF(D10=11.5,1.5,IF(D10=14.5,2,IF(D10=17.5,2.5,IF(D10=19.5,3,"")))))
2) If you want to get the result right in the status column, you can use the following formula:
=IF(B10<A10,CHOOSE(INT((B10+1-A10)*24*4/8.5),1,1,1,1,1.5,2,2,2.5,3),CHOOSE(INT((B10-A10)*24*4/8.5),1,1,1,1,1.5,2,2,2.5,3))
We hope this helps.
Suppose your in time is in a column and out time is in b column. And your data is in columns E F ang G ( G is that column where you have that 1 1.5 values updated) then paste below formula:
=INDEX($G$1:$G$5,MATCH($A1&$B1,$E$1:$E$5&$F$1:$F$5,0),1)
after pasting it press CTRL+SHIFT+Enter