PDA

View Full Version : Solved: VBA Select Statement



brorick
09-03-2006, 05:44 PM
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. :doh:

mdmackillop
09-04-2006, 12:08 AM
Hi Brorick,
Something similar going on here.
http://vbaexpress.com/forum/showthread.php?t=9305

Cyberdude
09-04-2006, 03:40 PM
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" .

brorick
09-05-2006, 06:18 AM
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.

Cyberdude
09-05-2006, 12:34 PM
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.

brorick
09-06-2006, 05:17 AM
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.

mdmackillop
09-06-2006, 02:24 PM
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!