Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Replace count totals

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    556
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Replace count totals

    Hi all, i would like to write a vba code in order to replace the count total in col. "P" so that where employees working hours are more than 4:51 should count 1 and where is less to 4:50 should count 0.50 (half). In my below extract the "2065 Count Total" should be 19.50.
    Thanking you in advance


    A B C D E O P
    1 I.D. Name Date IN OUT Total Time
    2 2065 EMPL. 2065 1/11/2017 7:45:20 17:43:31 9:58
    3 2065 EMPL. 2065 2/11/2017 7:46:06 18:01:07 10:15
    4 2065 EMPL. 2065 3/11/2017 7:57:06 18:11:06 10:14
    5 2065 EMPL. 2065 4/11/2017 7:46:34 16:25:49 8:39
    6 2065 EMPL. 2065 6/11/2017 7:39:45 11:30:20 3:91
    7 2065 EMPL. 2065 7/11/2017 7:45:18 17:43:49 9:58
    8 2065 EMPL. 2065 8/11/2017 7:43:07 17:26:02 9:43
    9 2065 EMPL. 2065 14/11/2017 7:43:54 17:54:35 10:11
    10 2065 EMPL. 2065 15/11/2017 8:11:19 16:50:17 8:39
    11 2065 EMPL. 2065 16/11/2017 7:55:30 17:29:32 9:34
    12 2065 EMPL. 2065 17/11/2017 7:43:36 12:30:20 4:87
    13 2065 EMPL. 2065 20/11/2017 7:43:31 17:04:09 9:21
    14 2065 EMPL. 2065 21/11/2017 7:40:08 16:41:41 9:01
    15 2065 EMPL. 2065 22/11/2017 7:41:35 17:20:32 9:39
    16 2065 EMPL. 2065 23/11/2017 7:39:23 17:02:59 9:23
    17 2065 EMPL. 2065 24/11/2017 7:44:40 16:45:47 9:01
    18 2065 EMPL. 2065 27/11/2017 7:43:02 16:52:56 9:09
    19 2065 EMPL. 2065 28/11/2017 7:50:06 17:39:08 9:49
    20 2065 EMPL. 2065 29/11/2017 7:42:02 17:16:47 9:34
    21 2065 EMPL. 2065 30/11/2017 9:06:36 17:30:12 8:24
    22 2065 Count 20
    23 2065 Total
    24
    25 2088 EMPL. 2088 7/11/2017 8:59:22 17:47:46 8:48

  2. #2
    Board Regular
    Join Date
    Nov 2017
    Location
    INDONESIA
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace count totals

    Row 6 3:50 0.5
    Row 12 4:46 0.5

    2065 total sum 19

    I'm using this formula

    Code:
    =IF(((E2-D2)*1440)>=291,1,0.5)

  3. #3
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    556
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace count totals

    Hi Ihart,
    Thank you for your support. The above formula works but can u explain me whats the mean of the numbers 1440 & 291? I know that the working monthly hours should be 173.33. However thanks once again for your help. Have a nice day

  4. #4
    Board Regular
    Join Date
    Nov 2017
    Location
    INDONESIA
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace count totals

    Those numbers I use to convert time value to decimal value.
    1440 is from minutes in 1 day (24 hours * 60 minutes )
    291 is from 4:51 * 1440

    Sorry about my english.

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    556
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace count totals

    Hi lhart, Thank you so much for explanation. Just i wanted to see the logic of the formula.
    I understand you very well and so do not worry about English. I am so glad, that you help me and you resolve my problem. It was very kind of you.
    All the best to you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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