Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts
    I have a spreadsheet to calculate nornal as well as overtime. The formula works well for all days, except as friday where the normal workuing hours are from 07:30 to 13:30 i.e 6 hours. I would like the formula emnded so that when it is a Friday the normal hours will be from 07:30 to 13:30, any time before 7:30 and after 1330 on a friday is considered overtime

    Your assistance will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In G8:

    =IF(OR(ISBLANK(E8),ISBLANK(F8)),"",IF(WEEKDAY(C8,2 )>5,0,24*(MIN(TIME(IF(WEEKDAY(C8,2)=5,13,16),30,0) ,F8)-MAX(TIME(7,30,0),E8))))

    In H8:

    =IF(OR(ISBLANK(E8),ISBLANK(F8)),"",IF(WEEKDAY(C8,2 )>5,24*(MIN(TIME(16,30,0),F8)-MAX(TIME(7,30,0),E8)),24*(MAX(TIME(7,30,0)-E8,0)+MAX(F8-TIME(IF(WEEKDAY(C8,2)=5,13,16),30,0),0))))

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Hans

    Thanks for the help

    Regards

    Howard

Posting Permissions

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