Problem with Time-Data format

[jgo1986]

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!

[SamT]

Use a custom Number Format on Column K: "[h]:m:s" If you want to see preceding zeros: "[h]:mm:ss"

[jgo1986]

Im sorry for not being able to answering sooner than today. You did not understand my question. K is not a column, it is a counter.
The problem is that I want to calculate the difference between 2 dates in hours, and this difference can be of more than 24h:

For instance (values in dd/mm/yyyy)
(26/11/2015 16:28) - (24/11/2015 14:38) = 49:50:15

When I calculate this difference, what I see on excel is 49:59:15, but when I do a click in the cell, the real value that appears is: 02/01/1900 1:50:15
This means: 24h of day 1/1/1900 + 24h of 02/01/1900 + 1:50:15 of the third day ...

If I calculate a date difference of less than 24 hours, the format is correct:
26/11/2015 19:02 - 26/11/2015 18:28 =0:34:12

If the difference between the 2 dates was 27/11/2015 19:02 - 26/11/2015 18:28 =24:34:12, what would appear in the cell would be 01/01/1900 0:34:12



What I need is to avoid representing the difference with the date "dd/01/1900". I need to represent times > 24h (300h, 400h... 1000h)...


Best regards,
Jgo.

[Charlize]

With a formula it could be something like this in column O when column N contains the difference between the dates formatted as hours. N4 = the row where you put this formula into O4 = should give > 72 or > 48 or > 24


=IF(VALUE(TEXT(N4;"[u]"))>72;"> 72";IF(VALUE(TEXT(N4;"[u]"))>48;"> 48";"> 24"))

Charlize

[jgo1986]

With a formula it could be something like this in column O when column N contains the difference between the dates formatted as hours. N4 = the row where you put this formula into O4 = should give > 72 or > 48 or > 24


=IF(VALUE(TEXT(N4;"[u]"))>72;"> 72";IF(VALUE(TEXT(N4;"[u]"))>48;"> 48";"> 24"))

Charlize


Thanks for your help. I'll try it. Anyway, I typed 01/01/1900 00:00, 01/01/1900 12:00 and 02/01/1900 00:00 and after changing the format to General, the values got represented as 1, 1.5 and 2, so I just compare the cells with that values and worked :D

Thanks for your time :)

[SamT]

Sorry, I was in a hurry when I read your question.

Excel stores the actual value of Dates and Times as decimal numbers (123.456...) and displays the dates as the difference between 1/1/1900 0:0:0 (0.000...) and the stored value. The Whole number part of the stored Value is days and the decimal part is times, (1/(24x60x60))



11/27/2015 10:38
-
11/24/2015 1:23
=
1/3/00 9:15:07
Date Format


42335.44315
-
42332.05766
=
3.38549289
NumberFormat