Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
time function
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
time function
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$100E1)*(B$1:B$100))) Mike "catrrmg" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
time function
Sorry but I don't get it...
"Mike H" wrote: 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$100E1)*(B$1:B$100))) Mike "catrrmg" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
time function
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 "Mike H" wrote: 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$100E1)*(B$1:B$100))) Mike "catrrmg" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
time function
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 -- HTH :) Dana DeLouis Windows XP & Excel 2007 "catrrmg" wrote in message ... 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 "Mike H" wrote: 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$100E1)*(B$1:B$100))) Mike "catrrmg" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an XLS function to convert std time to Military time? | Excel Worksheet Functions | |||
Macro / function text time to 24hr excel time passed midnight fortotaling hr's | Excel Worksheet Functions | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
Function Call Specified at Run Time rather than Compile Time? | Excel Programming | |||
Function to convert Time String to Time | Excel Worksheet Functions |