jgo1986
11-05-2015, 08:30 AM
Good afternoon,
I have made a Macro that, among other operations, calculates the difference between "now" Date-time and a past date-time. Both dates have the following format: 05/11/2015 9:20 (dd/mm/yy actual time). The result, stored in Column N of the difference between the dates is as follows:
1- If date difference is lower than 24h, both the cell content and the formula window shows "18:10:00"
2- If date difference is bigger than 24h, the cell content shows 498:15:00, but the formula shows something like "20/01/1900 18:15:00"
If the difference is bigger than 24h and lower than 48h, the value is something like "01/01/01 23:39:00"... so the value is 02/01/1900 18:10:00 if the difference is between 48 and 72h, 03/01/1900 18:10:00 if the difference is between 72 and 96h..
Once all differences between dates are store in each K row of the N Column, I need to classify the ranges (<24h, [24,36h], [36,38] and >48) in column O, so this is the code I made:
If (Sheets("Sheet").Range("N" & k).Value) <= "24:00:00" Then
Sheets("Sheet").Range("O" & k).Value = "<=24"
ElseIf (Sheets("Sheet").Range("N" & k).Value) <= "36:00:00" Then
Sheets("Sheet").Range("O" & k).Value = ">24 y <=36"
ElseIf (Sheets("Sheet").Range("N" & k).Value) <= "48:00:00" Then
Sheets("Sheet").Range("O" & k).Value = ">36 y <=24"
Else: Sheets("Sheet").Range("O" & k).Value = ">48"
End If
And this is the result in the sheet:
43:48:00
<=24
65:24:00
<=24
67:05:00
<=24
67:24:00
<=24
136:26:00
>48
140:14:00
>48
142:55:00
>48
215:43:00
>48
234:56:00
>48
236:00:00
>48
310:22:00
<=24
357:41:00
<=24
498:15:00
<=24
I believe that If I change the format of the Cells in column O from "20/01/1900 18:15:00" to 498:15:00, I would be able to calculate the range of hours.
May i have your help?
Thanks!
I have made a Macro that, among other operations, calculates the difference between "now" Date-time and a past date-time. Both dates have the following format: 05/11/2015 9:20 (dd/mm/yy actual time). The result, stored in Column N of the difference between the dates is as follows:
1- If date difference is lower than 24h, both the cell content and the formula window shows "18:10:00"
2- If date difference is bigger than 24h, the cell content shows 498:15:00, but the formula shows something like "20/01/1900 18:15:00"
If the difference is bigger than 24h and lower than 48h, the value is something like "01/01/01 23:39:00"... so the value is 02/01/1900 18:10:00 if the difference is between 48 and 72h, 03/01/1900 18:10:00 if the difference is between 72 and 96h..
Once all differences between dates are store in each K row of the N Column, I need to classify the ranges (<24h, [24,36h], [36,38] and >48) in column O, so this is the code I made:
If (Sheets("Sheet").Range("N" & k).Value) <= "24:00:00" Then
Sheets("Sheet").Range("O" & k).Value = "<=24"
ElseIf (Sheets("Sheet").Range("N" & k).Value) <= "36:00:00" Then
Sheets("Sheet").Range("O" & k).Value = ">24 y <=36"
ElseIf (Sheets("Sheet").Range("N" & k).Value) <= "48:00:00" Then
Sheets("Sheet").Range("O" & k).Value = ">36 y <=24"
Else: Sheets("Sheet").Range("O" & k).Value = ">48"
End If
And this is the result in the sheet:
43:48:00
<=24
65:24:00
<=24
67:05:00
<=24
67:24:00
<=24
136:26:00
>48
140:14:00
>48
142:55:00
>48
215:43:00
>48
234:56:00
>48
236:00:00
>48
310:22:00
<=24
357:41:00
<=24
498:15:00
<=24
I believe that If I change the format of the Cells in column O from "20/01/1900 18:15:00" to 498:15:00, I would be able to calculate the range of hours.
May i have your help?
Thanks!