IF Statements
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: IF Statements

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I work as an administrator using excel for team registers which are then reported via pivot tables for timesheet purposes.

    The problem I have is that several people work differing hours each day and on different days.

    I therefore can't fix a deduction of say 1 hour as it deducts this from all hours worked irrespective of the number.

    I need to be able to show if someone worked part of a day (3:30)and pay them for those hours and if more than 4 deduct 1 hour for lunch.Also required are the "H","A","S","U","OFF" AND "L"

    A copy of the file and the formula used is attached.

    Please help.



    09:00 13:30 17:00 16:55 03:25 1
    09:00 09:00 17:00 12:30 03:30 1
    09:00 09:10 17:00 16:50 07:40 1
    09:00 09:00 17:00 17:00 H H
    09:00 09:05 17:00 16:55 07:50 1
    09:00 09:00 17:00 16:50 07:50 1
    09:00 09:00 17:00 17:00 08:00 1


    =IF(J9=1,H9-F9,IF(J9="OFF","OFF",IF(J9="U","U",IF(J9="A","A",IF(J9="S","S",IF(J9="H","H",IF(J9="T","T",IF(J9="L","L"))))))))


  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    09:00 13:30 17:00 16:55 03:25 1
    09:00 09:00 17:00 12:30 03:30 1
    09:00 09:10 17:00 16:50 07:40 1
    09:00 09:00 17:00 17:00 H H
    09:00 09:05 17:00 16:55 07:50 1
    09:00 09:00 17:00 16:50 07:50 1
    09:00 09:00 17:00 17:00 08:00 1
    Not really following your data, what are your columnn headers? I'm guessing there's something like, "Time In". "Time Out" etc. but I'm not quite sure.

    Additional question:
    Tim/Hun/Partick or QP?

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-03-14 07:36, Mark O'Brien wrote:

    09:00 13:30 17:00 16:55 03:25 1
    09:00 09:00 17:00 12:30 03:30 1
    09:00 09:10 17:00 16:50 07:40 1
    09:00 09:00 17:00 17:00 H H
    09:00 09:05 17:00 16:55 07:50 1
    09:00 09:00 17:00 16:50 07:50 1
    09:00 09:00 17:00 17:00 08:00 1
    Not really following your data, what are your columnn headers? I'm guessing there's something like, "Time In". "Time Out" etc. but I'm not quite sure.

    Additional question:
    Tim/Hun/Partick or QP?

    [SCHED START, ACTUAL START,SCHED FINISH,ACTUAL FINISH, TIME PRESENT]


    Time value calculated on finish time less start time. and value is dependent on data entered in (present) column

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-03-14 07:36, Mark O'Brien wrote:

    09:00 13:30 17:00 16:55 03:25 1
    09:00 09:00 17:00 12:30 03:30 1
    09:00 09:10 17:00 16:50 07:40 1
    09:00 09:00 17:00 17:00 H H
    09:00 09:05 17:00 16:55 07:50 1
    09:00 09:00 17:00 16:50 07:50 1
    09:00 09:00 17:00 17:00 08:00 1
    Not really following your data, what are your columnn headers? I'm guessing there's something like, "Time In". "Time Out" etc. but I'm not quite sure.

    Additional question:
    Tim/Hun/Partick or QP?

    [is that relevant: congratulations where it due]

  5. #5
    Guest

    Default

    OK, there's probably an easier way to do this, but this is my suggestion.

    Find a nice empty part of your spreadsheet and put the following data in the two columns:


    I think in your example you were wanting J9 to hold one of the values(0,1,H,A,S,OFF or L)

    So in J9 we're going to put a formula like this in :

    =VLOOKUP(J9,$N$3:$O$9,2)

    The thing to change here is the Range ($N$3:$O$9). You will change this to be the range that we put data into earlier.

    This should work to give you

    0 if Hours < 4
    1 if hours >= 4
    and return whatever text is required. I really hope this works, usually I'm just a VBA guy, but since you're from Glasgow I took pity on you. (especially as I think your leanings may be towards the Dark Side)

    Any problems, just repost.

    "Gonnae no' dae that"

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That anonymous posting was me.

    Cheers

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-03-14 08:19, Anonymous wrote:
    OK, there's probably an easier way to do this, but this is my suggestion.

    Find a nice empty part of your spreadsheet and put the following data in the two columns:


    I think in your example you were wanting J9 to hold one of the values(0,1,H,A,S,OFF or L)

    So in J9 we're going to put a formula like this in :

    =VLOOKUP(J9,$N$3:$O$9,2)

    The thing to change here is the Range ($N$3:$O$9). You will change this to be the range that we put data into earlier.

    This should work to give you

    0 if Hours < 4
    1 if hours >= 4
    and return whatever text is required. I really hope this works, usually I'm just a VBA guy, but since you're from Glasgow I took pity on you. (especially as I think your leanings may be towards the Dark Side)

    Any problems, just repost.

    "Gonnae no' dae that"]
    ["How no"]

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    [quote]
    On 2002-03-14 08:23, JIMMARSHALL6660 wrote:
    [quote]
    On 2002-03-14 08:19, Anonymous wrote:
    OK, there's probably an easier way to do this, but this is my suggestion.

    Find a nice empty part of your spreadsheet and put the following data in the two columns:


    I think in your example you were wanting J9 to hold one of the values(0,1,H,A,S,OFF or L)

    So in J9 we're going to put a formula like this in :

    =VLOOKUP(J9,$N$3:$O$9,2)

    The thing to change here is the Range ($N$3:$O$9). You will change this to be the range that we put data into earlier.

    This should work to give you

    0 if Hours < 4
    1 if hours >= 4
    and return whatever text is required. I really hope this works, usually I'm just a VBA guy, but since you're from Glasgow I took pity on you. (especially as I think your leanings may be towards the Dark Side)

    Any problems, just repost.

    "Gonnae no' dae that"]
    ["How no"]
    [Can't get this to work.

    I'll attach a spreadsheet showing what i'm trying to do if you want to email me maybe someone outthere can help
    ]

User Tag List

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
  •  

 

 
DMCA.com