mtairhead
Registered User.
- Local time
- Today, 12:26
- Joined
- Oct 17, 2003
- Messages
- 138
I'm having a bit of problem with a spreadsheet. I'm using VBA to setup a sheet. One of the columns is annoying me. Column B is "Start Time" and Column C is "End time" Column Z (We just skipped a bit) is the number of hours between the start and end time; the total hours worked.
So, cell Z4 might have the formula...
If I set the formula up manually, I don't have a problem, but when I run the VBA to do it, half of the values work as predicted, and half find the difference between the NUMERICAL value of the times in columns B and C (Like .333 for 8:00 AM and 3.71 for 5:00 PM) and then multiplies that by 24, giving a very large number (81, for example, when I use 8:00 AM and 5:00 PM).
Columns B and C are formatted to Time, and column Z is formatted as a number with two decimals. The VBA that sets all of column Z is as follows:
If you have any ideas, please let me know!
~Andrew
So, cell Z4 might have the formula...
PHP:
= if((C4-B4)*24>0,(C4-B4)*24,"")
If I set the formula up manually, I don't have a problem, but when I run the VBA to do it, half of the values work as predicted, and half find the difference between the NUMERICAL value of the times in columns B and C (Like .333 for 8:00 AM and 3.71 for 5:00 PM) and then multiplies that by 24, giving a very large number (81, for example, when I use 8:00 AM and 5:00 PM).
Columns B and C are formatted to Time, and column Z is formatted as a number with two decimals. The VBA that sets all of column Z is as follows:
PHP:
Sheets("Results").Range("Z2:Z2").Value = "= if((C2-B2)*24>0,(C2-B2)*24,"""")"
Sheets("Results").Range("Z2:Z2").Copy
Sheets("Results").Range("Z3:Z32767").PasteSpecial xlPasteAll
If you have any ideas, please let me know!
~Andrew