Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Location
    Scotland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula request (Excel 97 & 2000)

    Hello. I am hoping to use excel for an attendance register at work and count up everyone's hours/shift etc. I am looking for a formula that will recognise what sort of shift someone has worked. In say cells C2:C9 I have the type of shift for each day of that week , D for day shift, N for night shift, AL for leave etc etc. and in D29 I have the hours worked for that shift pattern. I then have the same in F2:F9 for the following week. A month per worksheet.
    Below all this I hopefully would like a formula that would be able to search/lookup each weekly column to give the total hours associated to each shift type. D shift 27 hours, AL 8.5 hours, Admin 8 hours etc.
    Hope I explained thinks OK if not I can supply more info, hopefully.
    Cheers <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula request (Excel 97 & 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 26-Feb-05 06:51. Changed post with code to the updated version)</P>I think your question is very similar to <post#=380774>post 380774</post#> and you might be able to adapt the solutions found in that thread. The most pertinent is probably the function in <post#= 380915>post 380915</post#> which could be modified to your specific needs.

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Location
    Scotland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula request (Excel 97 & 2000)

    I tried to modify from related post but got more confused. I think I am missing something simple, Will the IF function best fit my problem do you think?
    AJM

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula request (Excel 97 & 2000)

    AJM,

    How bout something like this...

    Brent

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Location
    Scotland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula request (Excel 97 & 2000)

    Brent
    Thanks a lot, that exactly what I was looking for. I can adapt that now. It was the SUMIF function I required.
    Thanks for doing this
    Andrew

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula request (Excel 97 & 2000)

    Andrew,

    Never a problem. This is a great place. Someone will come up with an answer or two for most any question. Usually it is a race to get your reply in before someone else beats you to it. When I have a question or need advice for a problem I am working on, this is the first place I look to.

    Brent

Posting Permissions

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