Consulting

Results 1 to 7 of 7

Thread: Solved: VBA Select Statement

  1. #1

    Solved: VBA Select Statement

    Hello. I have a timesheet I am attempting to convert to vba. In my total column I have this formula =((F4-C4+(F4 < C4))-(E4-D4+(E4 < D4)))*24. I am trying to convert it to a select statement. My attachment has an example.

    By the way i noticed that if a user enters 12:00 am as a start time it will not show up in the cell but it does appear in the formula bar. I know that if the user enters 24:00 then I will see 12:00 am in the cell. Most of the users do not care for military time so I have to stick with the civilian time format. Any thoughts as to why this occurs.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Brorick,
    Something similar going on here.
    http://vbaexpress.com/forum/showthread.php?t=9305
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    I think some of your problems are due to cell formatting.

    The bottom cell is formatted as "[h]:mm". That should be used only when you are looking for, say, time differences as a result of subtraction. You should format a time entry cell as ordinary "h:mm AM/PM". That also accounts for why you don't see the "PM" when you enter "12:00". The cell you entered it into wasn't properly preformatted with "h:mm AM/PM" .

  4. #4
    The first part of my question has been resolved. But, the reference to 12:00 midnight is still an issue even after I followed Cyberdude's instructions. I am still puzzled as to why this occurs in Excel 2003.

  5. #5
    brorick, I misread your question. I thought you said the user enters "12:00", but you wrote "12:00 am". Sorry. However, I just tested it by entering "12:00 am" and Excel displayed "12:00 AM" in the cell (it changed lowercase "am" to uppercase "AM". In the formula bar it displayed "12:00:00 AM". When I checked the format, it showed "h:mm AM/PM". So I'm not sure what is happening when you do it. However, when I entered "12:00" (no "am"), then I get the same thing you get. It sounds like to me that your user is omitting the "am". He MUST enter "am" or "pm" to get the correct value.

  6. #6
    Cyberdude thank you for your help. I was asked to modify an existing workbook. What I didn't know was that the previous user had applied conditional formatting to hide all zeros by applying white to the font color so that it would blend in the background. That's why I could see it in the formula bar but not in the cell. It was by far the most frustrating thing I had ever experienced and yet it was so simple to fix. Thanks for your help.

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Quote Originally Posted by brorick
    It was by far the most frustrating thing I had ever experienced
    Hi Brorick,
    I once in my youth (Windows 3.1) turned the Windows text colour to white. Even more fun!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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