time function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

catrrmg - 20 Sep 2007 14:06 GMT

hi all. I've got the following situation: I have a column with operations and
a next one containg the time they occured (time format 00:00:00), what I want
is to determine a standart time interval for every occurence, eg: 08:15:12
should result 08:00 - 09:00 and so on, with 1 hour steps. I now I should
create a function and use the select statement but have no clue how to do
send the tim to the vba, process it and send it back to the cell. any help
will be much appreciated.

Reply to this Message

Mike H - 20 Sep 2007 14:26 GMT

Hi,

Put your time range in 2 columns like this:-

Col D Col E
08:00    09:00
09:00    10:00
10:00    11:00

The put this formula in a cell and drag down
=SUMPRODUCT(--(A$1:A$100>=D1)*(B$1:B$100))-(SUMPRODUCT(--(A$1:A$100>E1)*(B$1:B$100)))

Mike

> hi all. I've got the following situation: I have a column with operations and
> a next one containg the time they occured (time format 00:00:00), what I want
[quoted text clipped - 3 lines]
> send the tim to the vba, process it and send it back to the cell. any help
> will be much appreciated.

Reply to this Message

catrrmg - 20 Sep 2007 14:36 GMT

Sorry but I don't get it...

> Hi,
>
[quoted text clipped - 17 lines]
> > send the tim to the vba, process it and send it back to the cell. any help
> > will be much appreciated.

Reply to this Message

catrrmg - 20 Sep 2007 15:14 GMT

this is what I came up with:
Public Function time_zone(tz) As String
Select Case tz
Case 0.291666666666667 To 0.333333333333333
time_zone = "07:00 - 08:00"
Case 0.333333333333333 To 0.375
time_zone = "08:00 - 09:00"
Case 0.375 To 0.416666666666667
time_zone = "09:00 - 10:00"
Case 0.416666666666667 To 0.458333333333333
time_zone = "10:00 - 11:00"
Case 0.458333333333333 To 0.5
time_zone = "11:00 - 12:00"
Case 0.5 To 0.541666666666667
time_zone = "12:00 - 13:00"
Case 0.541666666666667 To 0.583333333333333
time_zone = "13:00 - 14:00"
Case 0.583333333333333 To 0.625
time_zone = "14:00 - 15:00"
Case 0.625 To 0.666666666666667
time_zone = "15:00 - 16:00"
Case 0.666666666666667 To 0.708333333333333
time_zone = "16:00 - 17:00"
Case 0.708333333333333 To 0.75
time_zone = "17:00 - 18:00"
Case 0.75 To 0.791666666666667
time_zone = "18:00 - 19:00"
Case 0.791666666666667 To 0.833333333333333
time_zone = "19:00 - 20:00"
Case 0.833333333333333 To 0.875
time_zone = "20:00 - 21:00"
Case 0.875 To 0.916666666666667
time_zone = "21:00 - 22:00"
End Select
End Function

> Hi,
>
[quoted text clipped - 17 lines]
> > send the tim to the vba, process it and send it back to the cell. any help
> > will be much appreciated.

Reply to this Message

Dana DeLouis - 20 Sep 2007 16:29 GMT

Could you join these two statements into something you can use? Perhaps add
formatting.

Sub Demo()
Dim t As Date 'Time
Dim H As Long 'Hour
t = 0.292
H = Hour(t)
Debug.Print TimeSerial(H, 0, 0)
Debug.Print TimeSerial(H + 1, 0, 0)
End Sub

Signature

HTH :>)
Dana DeLouis
Windows XP & Excel 2007

> this is what I came up with:
> Public Function time_zone(tz) As String
[quoted text clipped - 59 lines]
>> > help
>> > will be much appreciated.

Reply to this Message