Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
performing calculations on time data
I have googled and searched this group and have not found exactly what i
am looking for. everything i have seen is for performing functions in the worksheet and not in vba code. i'll set up the prolbem, explain what i need, and give you what i have come up with so far. i have two columns with time data. i want the third column to show the difference between the time. so if one 13:30 and the other is 14:00 i want the 3rd column to show :30. or if one is 14:00 and the other is 13:30 i want the 3rd column to show :30 A B C 13:30 14:00 :30 14:00 13:30 :30 the all cells are formatted as general. the code snippets below are for excel2000. If .Cells(row, colSchedule).Value .Cells(row, colActual).Value Then .Cells(row, colVariance).Value = .Cells(row, colSchedule).Value - _ .Cells(row, colActual).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" Else .Cells(row, colVariance).Value = .Cells(row, colActual).Value - _ .Cells(row, colSchedule).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" End if i have also attempted using Range(.cells(row,colSchedule).value etc.... in both attempts the comparison works fine (which boggles my mind), however the cell assignment/mathematic operation fails with a mismatch object error. all cells formatted as general. i don't know if that is the problem, but i doubt it since if i do this in the sheet, eg =B1-A1, i get no problem. The code does not make it to the Range......NumberFormat so i have no idea if that will work or not. I do not think the builtin Time function(time(h,m,s) will work since i cant seperate the information by hour.minutes. i have read how excel stores time data, but i do not (hope not) think that applies. what this is not is a payroll sheet or some sort of userform. based on the if statements above some other things get processed and sorted out, i have not included them for brevity. there will also be a future comparison based on the result of the calculation to the effect of if it is 30 then something happens, if it < 30 something else happens. i hope this is clear. TIA bst |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
performing calculations on time data
I made some minor changes
1) You can't use ROW as a variable it is reserved. Change to RowCount 2) You didn'ty have a period in front of Range on the two lines with Numberformat 3) Your Numberformat lines had Cells and Range. You can't have both Sub test() RowCount = 1 colSchedule = "A" colActual = "B" colVariance = "C" With ActiveSheet Do While .Range(colSchedule & RowCount) < "" If .Cells(RowCount, colSchedule).Value _ .Cells(RowCount, colActual).Value Then .Cells(RowCount, colVariance).Value = _ .Cells(RowCount, colSchedule).Value - _ .Cells(RowCount, colActual).Value .Cells(RowCount, colVariance).NumberFormat = "hh:mm" Else .Cells(RowCount, colVariance).Value = _ .Cells(RowCount, colActual).Value - _ .Cells(RowCount, colSchedule).Value .Cells(RowCount, colVariance).NumberFormat = "hh:mm" End If RowCount = RowCount + 1 Loop End With End Sub "bst" wrote: I have googled and searched this group and have not found exactly what i am looking for. everything i have seen is for performing functions in the worksheet and not in vba code. i'll set up the prolbem, explain what i need, and give you what i have come up with so far. i have two columns with time data. i want the third column to show the difference between the time. so if one 13:30 and the other is 14:00 i want the 3rd column to show :30. or if one is 14:00 and the other is 13:30 i want the 3rd column to show :30 A B C 13:30 14:00 :30 14:00 13:30 :30 the all cells are formatted as general. the code snippets below are for excel2000. If .Cells(row, colSchedule).Value .Cells(row, colActual).Value Then .Cells(row, colVariance).Value = .Cells(row, colSchedule).Value - _ .Cells(row, colActual).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" Else .Cells(row, colVariance).Value = .Cells(row, colActual).Value - _ .Cells(row, colSchedule).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" End if i have also attempted using Range(.cells(row,colSchedule).value etc.... in both attempts the comparison works fine (which boggles my mind), however the cell assignment/mathematic operation fails with a mismatch object error. all cells formatted as general. i don't know if that is the problem, but i doubt it since if i do this in the sheet, eg =B1-A1, i get no problem. The code does not make it to the Range......NumberFormat so i have no idea if that will work or not. I do not think the builtin Time function(time(h,m,s) will work since i cant seperate the information by hour.minutes. i have read how excel stores time data, but i do not (hope not) think that applies. what this is not is a payroll sheet or some sort of userform. based on the if statements above some other things get processed and sorted out, i have not included them for brevity. there will also be a future comparison based on the result of the calculation to the effect of if it is 30 then something happens, if it < 30 something else happens. i hope this is clear. TIA bst |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
performing calculations on time data
On May 27, 4:04 pm, bst wrote:
I have googled and searched this group and have not found exactly what i am looking for. everything i have seen is for performing functions in the worksheet and not in vba code. i'll set up the prolbem, explain what i need, and give you what i have come up with so far. i have two columns with time data. i want the third column to show the difference between the time. so if one 13:30 and the other is 14:00 i want the 3rd column to show :30. or if one is 14:00 and the other is 13:30 i want the 3rd column to show :30 A B C 13:30 14:00 :30 14:00 13:30 :30 the all cells are formatted as general. the code snippets below are for excel2000. If .Cells(row, colSchedule).Value .Cells(row, colActual).Value Then .Cells(row, colVariance).Value = .Cells(row, colSchedule).Value - _ .Cells(row, colActual).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" Else .Cells(row, colVariance).Value = .Cells(row, colActual).Value - _ .Cells(row, colSchedule).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" End if i have also attempted using Range(.cells(row,colSchedule).value etc.... in both attempts the comparison works fine (which boggles my mind), however the cell assignment/mathematic operation fails with a mismatch object error. all cells formatted as general. i don't know if that is the problem, but i doubt it since if i do this in the sheet, eg =B1-A1, i get no problem. The code does not make it to the Range......NumberFormat so i have no idea if that will work or not. I do not think the builtin Time function(time(h,m,s) will work since i cant seperate the information by hour.minutes. i have read how excel stores time data, but i do not (hope not) think that applies. what this is not is a payroll sheet or some sort of userform. based on the if statements above some other things get processed and sorted out, i have not included them for brevity. there will also be a future comparison based on the result of the calculation to the effect of if it is 30 then something happens, if it < 30 something else happens. i hope this is clear. TIA bst This little test worked for me in Excel 2003... Sub test() With ActiveWorkbook.Sheets(1) .Cells(2, 3).Value = .Cells(2, 2).Value - .Cells(2, 1).Value .Cells(2, 3).NumberFormat = "hh:mm" End With End Sub Note that I removed the Range() reference as it was not valid in this context. I also note that a simple subtraction in column C as a worksheet function worked as well in Excel 2003. Excel automatically converted the 14:00 and 13:30 to hh:mm format upon entry and performed the correct math on the underlying time. When the format was adjusted back to General, the values in the three cells were 0.5625, 0.5833 and 0.02083. That might explain the problem you are having (or will have) in testing against <30 or more than 30 minutes. There are two internal VBA functions you probably want to understand: TimeValue() and DiffDate() in order to perform this comparison. They might also apply to your problem in subtracting the numbers in version 2000 (if it doesn't perform the time conversion automatically, as appears to be the case from the type mismatch problem). In that case, you might need to use something like ... Sub test() With ActiveWorkbook.Sheets(1) .Cells(2, 3).Value = TimeValue(.Cells(2, 2).Value) - TimeValue(.Cells(2, 1).Value) .Cells(2, 3).NumberFormat = "hh:mm" End With End Sub HTH, Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
performing calculations on time data
On Tue, 27 May 2008 14:38:14 -0700, T Lavedas wrote:
<snip 13:30 14:00 :30 14:00 13:30 :30 the all cells are formatted as general. the code snippets below are for excel2000. If .Cells(row, colSchedule).Value .Cells(row, colActual).Value Then .Cells(row, colVariance).Value = .Cells(row, colSchedule).Value - _ .Cells(row, colActual).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" Else .Cells(row, colVariance).Value = .Cells(row, colActual).Value - _ .Cells(row, colSchedule).Value Range(.Cells(row, colVariance)).NumberFormat = "hh:mm" End if <snip TIA bst <snip That might explain the problem you are having (or will have) in testing against <30 or more than 30 minutes. There are two internal VBA functions you probably want to understand: TimeValue() and DiffDate() in order to perform this comparison. They might also apply to your problem in subtracting the numbers in version 2000 (if it doesn't perform the time conversion automatically, as appears to be the case from the type mismatch problem). In that case, you might need to use something like ... Sub test() With ActiveWorkbook.Sheets(1) .Cells(2, 3).Value = TimeValue(.Cells(2, 2).Value) - TimeValue(.Cells(2, 1).Value) .Cells(2, 3).NumberFormat = "hh:mm" End With End Sub HTH, Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ Thanks for your reply. I want to verify that i understand correctly. the code is not necessarily wrong for what i want to do, but maybe excel2000 does not do what i want it to do. that is better to know. what bothers me is that in the worksheet it will do the conversion automatically, and it looks as if i will have to do them manually in the code. i will investigate the two functions you mentioned. do you think the comparison in the if statement is working correctly, even though the math is not? i followup after i have attempted these suggestions. thanks bst |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
performing calculations on time data
T Lavedas wrote in
: That might explain the problem you are having (or will have) in testing against <30 or more than 30 minutes. There are two internal VBA functions you probably want to understand: TimeValue() and DiffDate() in order to perform this comparison. They might also apply to your problem in subtracting the numbers in version 2000 (if it doesn't perform the time conversion automatically, as appears to be the case from the type mismatch problem). In that case, you might need to use something like ... Sub test() With ActiveWorkbook.Sheets(1) .Cells(2, 3).Value = TimeValue(.Cells(2, 2).Value) - TimeValue(.Cells(2, 1).Value) .Cells(2, 3).NumberFormat = "hh:mm" End With End Sub HTH, Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ the timevalue function works great. i am ashamed that i did not find it on my own. you would think that would show up somehwhere in the google search. i did search the object browser but of course i forgot to simply search help. i think it is the second function listed. you were a great help. thanks bst |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Performing Calculations of data starting with Greater Than | Excel Discussion (Misc queries) | |||
Performing calculations if a value is between A and B | Excel Worksheet Functions | |||
Performing calculations on only cells with data | Excel Worksheet Functions | |||
Help on performing calculations on formula results | Excel Worksheet Functions | |||
Excel VBA macros and real time data calculations | Excel Programming |