Results 1 to 9 of 9

Thread: Time calculation-Event hrs and event hrs by munites

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker ABC111's Avatar
    Join Date
    Sep 2021
    Excel Version

    Time calculation-Event hrs and event hrs by munites


    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)," "))))

    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts