Hello,
I need help to calculate event hour and event hour by minutes base on event occurred. Base on date and time on sheeh1. we need to calculate and populate event by hours and event hour by minutes.
Shee1: Column A has DATE;
Column E has time on;
Column F has time off
Sheet 2:Currenly we enter manually in column H(Event by HE) and Column I(Event minutes per HE).
I did try to apply formula in Column N and its working but this formula need to be update that can work for next date as per sheet 1 as well next event and event can be happened more than 1 per day. There might be possible that event can't be happened every day.
Column N : =IF(C14<=C15,IF(HOUR(TIME(RIGHT(A14,2),0,0))=(HOUR(Sheet1!$E$7)),Sheet1!$E$7,IF(HOUR(TIME(RIGHT(A14,2),0,0))=(HOUR(Sheet1!$F$7)),Sheet1!$F$7,IF(AND(HOUR(TIME(RIGHT(A14,2),0,0))>HOUR(Sheet1!$E$7),HOUR(TIME(RIGHT(A14,2),0,0))<HOUR(Sheet1!$F$7)),TIME(RIGHT(A14,2),0,0)," "))))
Thanks,
I need help to calculate event hour and event hour by minutes base on event occurred. Base on date and time on sheeh1. we need to calculate and populate event by hours and event hour by minutes.
Shee1: Column A has DATE;
Column E has time on;
Column F has time off
Sheet 2:Currenly we enter manually in column H(Event by HE) and Column I(Event minutes per HE).
I did try to apply formula in Column N and its working but this formula need to be update that can work for next date as per sheet 1 as well next event and event can be happened more than 1 per day. There might be possible that event can't be happened every day.
Column N : =IF(C14<=C15,IF(HOUR(TIME(RIGHT(A14,2),0,0))=(HOUR(Sheet1!$E$7)),Sheet1!$E$7,IF(HOUR(TIME(RIGHT(A14,2),0,0))=(HOUR(Sheet1!$F$7)),Sheet1!$F$7,IF(AND(HOUR(TIME(RIGHT(A14,2),0,0))>HOUR(Sheet1!$E$7),HOUR(TIME(RIGHT(A14,2),0,0))<HOUR(Sheet1!$F$7)),TIME(RIGHT(A14,2),0,0)," "))))
Thanks,