Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Upper and lower case calculations (2003 SP2)

    Good morning

    In the (very scaled down) leave planner dates are added by calling up the user form. The 'LeaveTypes' range takes it value from a horizontal range in columns AN:AU. Column E matches the type of leave selected using numeric values. Columns h through to AL add the values into the planner.

    I have been asked to include the facility to add 1/2 days leave as at the moment I can only allow for full days taken

    Could the formulas be changed to recognise upper and lower case Hh, Ss - Upper = 1, Lower = 0.5
    Could the 'Countifs' in AN7:AU7 etc. be altered to count the 1's and the 0.5's
    Could the code in the userform be changed to show both Hh, Ss etc.

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Upper and lower case calculations (2003 SP2)

    COUNTIF is not case-sensitive, i.e. COUNTIF(H7:AL7,"H") counts both "h" and "H".

    I'd use two cells per day, for AM and PM. Then it's easy to adjust the formulas. Adapting the userform would be more difficult.
    Attached Images Attached Images
    • File Type: png x.PNG (1.5 KB, 0 views)

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper and lower case calculations (2003 SP2)

    I use 'H' and 'h' in our holiday planner, using:

    <code>=(GetCount(J6:J30,"H"))+(GetCount(J6:J30,"h" )*0.5)</code>

    ( <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>...which I'm sure Hans pointed me to many holiday moons ago!)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Upper and lower case calculations (2003 SP2)

    GetCount is a custom function written by Legare Coleman - see <post:=67,044>post 67,044</post:>.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Upper and lower case calculations (2003 SP2)

    Is this the sort of thing? I only added 'h' but the principle is the same for other lower case letters.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper and lower case calculations (2003 SP2)

    Ah yes - thanks for the reminder!

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper and lower case calculations (2003 SP2)

    Hi Rory

    Thanks for your help, it works perfect ion the stripped down version but implementing it into the full blown versions is giving me an #Value! error. I had to insert a couple of new lines I changed the formula to

    =SUM(IF(ISERROR($J7:$AN7),"",--EXACT($J7:$AN7,AP$5))) but It seems to have caused a problem

    Can you see what I have done wrong to get the error?

    Cheers

    Steve

    Thanks to everybody else for chipping in
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Upper and lower case calculations (2003 SP2)

    Did you array-enter the formula (with Ctrl+Shift+Enter)?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper and lower case calculations (2003 SP2)

    Hi Rory

    No, I typed it into AP7 directly and pressed enter, but doing it again and pressing Ctrl+shift+enter and it is now OK

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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