Time calculation and display

[skiran]

HI I need help with following .

say user inputs time as 1:35 am -4:34pm and student category as -initial
- Need to identify what category it is ( we have 5 diff. categories)
- Need to round up and down as 1 -5 (round down start time and round up end time)
- Now in another worksheet in same work book ( i have another worksheet with matrix of hrs and categories )have to display "yes " against category intial and hrs 1:00 pm, 2:00 pm . 3:00 pm . 4:00 pm and 5:00pm

 

[Misca]

Here's one way to do it:

=IF(AND($A$2=$I17,HOUR(J$16)>=HOUR($B$2),HOUR(J$16)<=HOUR($C$2+(59/24/60))),"YES","")

where user inputs are in A2:C2 (= category, starting time and ending time) and the matrix category names are found in I17:I21 and matrix hours can be found from J16-> on the same row).
Type the formula in J17 and copy to fill the whole matrix.

 

[skiran]

Thank you , it worked. Now if i want to extend this scenario in the sense that
- There are 15 rows of user input categories and times( start and end times ) and insted of yes/no need to display the "number count " each time the above condition was true for each hr .