Bad Time Values

[mtairhead]

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((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:

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

 

[Brianwarnock]

I guess this doesn't help but i copied your formaula a nd ran it on a spreadsheet typing in times and even dates 01/01/07 013/01/07 (uk) and got 48.00 , just could not get an error.

I'm not going to be around for a few days but i think that you may have to attach your spreadsheet for someone to look at as obviously there is no intrinsic error.

Brian

Edit 5:00 PM is not 3.71 it is .71 the 3 is a date, maybe that's a clue.

 

[mtairhead]

Edit 5:00 PM is not 3.71 it is .71 the 3 is a date, maybe that's a clue.

A clue indeed. That solved the case.

Those cells with a date included (but not shown, due to formatting) seem to work fine. Those cells with just the time have problems.

Thanks! I've got it now.

~Andrew