Results 1 to 7 of 7

Thread: Counting

  1. #1
    Join Date
    Apr 2012
    Posts
    5

    Unanswered: Counting

    Hi friends, please help me with a solution:

    I have a excel sheet in which column A has dates,column B has criteria (either P1 OR P2) ,COLUMN C has time on, column D has time off, column E has total time.

    Note: Day (06:00 till 22:00) NIGHT ( 22:00 till 06:00)

    I would like to calculate the summary monthwise for day timing and night timings for P1 as well as P2.
    Please find the attachment .
    Attached Thumbnails Attached Thumbnails PROBLEM.bmp  

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi,

    Please find attached file with what I think you are looking for. In particular it is worth understanding the following features...

    1. The fact that Excel stores a time as a proportion of the 24H day. So 06:00 would be 0.25 and 18:00 would be 0.75

    2. The use of the MOD function to ensure that the value given for hours worked is not a negative value - MOD(number, divisor) = number - divisor *INT(number/divisor)

    3. The setting of the hurdle whereby day shifts end and night shifts begin - in your case 22:00
    Attached Files Attached Files
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Apr 2012
    Posts
    5

    Thanks

    Dear Dave thanks a lot for the solution...But i am actually have to enter the time in hours and minutes. How to solve that one ?

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    It will not make any difference. In all of the formulae simply remove anything that is multiplied by 24 as this will give you the whole number of hours. To display the number of hours and minutes you just need to format the cell as a time and it will display as such.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  5. #5
    Join Date
    Apr 2012
    Posts
    5

    Thanks

    Dear Dave,

    I got the point.

    As you have taken the range as $C$3 to $C$19, how shall i get the range with the month's name.
    I mean i want the summary monthwise.

  6. #6
    Join Date
    Jan 2012
    Posts
    97
    =SUMPRODUCT(--($C$3:$C$20="P1"),--($B$3:$B$20>DATE(2012,12,1)),--(DATE(2012,12,31)>$B$3:$B$20),$I$3:$I$20)

    You'll need to change the dates to give you the sub totals for each month.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  7. #7
    Join Date
    Apr 2012
    Posts
    5

    Thanks

    Dear Dave,

    Thanks a lot for the help.
    I have got the solution that after 22:00 it will be night.

    Now the problem exists that night timing shall be from 22:00 till morning 07:00.


    How shall i approach for the solution ?
    many thanks in advance.

    Regds

Tags for this Thread

Posting Permissions

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