Grouping Log In Times Into 15 Minute Intervals

May 12, 2009

I have been given the task of finding out who logs in at what time of the day.

I have attached the raw data that I have to work with.

In essence I need to see how many login's there were for 06.00 - 10.00 at 15 minute intervals e.g:

06:00 - 5 Logins
06:15 - 1 Logins
06:30 - 3 Logins
06:45 - 11 Logins
etc

Has anyone got any suggestions?

View 6 Replies


ADVERTISEMENT

Sorting Data To 15 Minute Intervals?

Jan 11, 2014

In the attached excel file I'd like to use data that was polled every 15 minutes. i.e 00:15, 00:30, 00:45, etc. Can I do this without having to manually delete all the nonrequired data per each row?

Also I have many files of such data that and would like to combine the all them into one to represent data for the whole year polled at every 15 minutes.

View 2 Replies View Related

Convert Day:Hour:Minute To Valid Times

Sep 12, 2007

We have a report application and developers that are giving us a real challenge.

1) if you have a cell formatted with:

DD:HH:MM and have a 4 digit numeric value for each section:
47.00:0.00:59.00
you can use the following formula to convert to hours minutes that excel can handle:
=(LEFT(P18,4))+MID(P18, FIND(":",P18)+1,4)/24+MID(P18, FIND(":",P18,7)+1,4)/1440

2) if you have a cell formatted with:
DD:HH:MM and have a 2 digit numeric value for each section:
47:00:59
you can use the following formula to convert to hours minutes that excel can handle:
=(LEFT(P4,2))+MID(P4, FIND(":",P4)+1,2)/24+MID(P4, FIND(":",P4,5)+1,2)/1440

Problem:
our developers have created a new report that now has a variable format
D:H:M
2:6:33
4:3:3
0:22:5
0:2:2
22:0:1..................

View 8 Replies View Related

Sum Times During Multiple Intervals?

Mar 18, 2014

I'm trying to determine how much time our agents are spending for their lunch/breaks during 30 minute intervals. Range A8:A200 lists the agent's names, Range B8:B200 lists their lunch/break start times, Range C8:C200 lists their lunch/break end times, and Column E lists the times (8:00, 8:30, 9:00, etc.). If an agent starts their break at 10:57 (Column B) and ends at 11:10 (Column C), the value next to 10:30 (Column E) would have 0:03:00 and the value next to 11:00 would have 0:10:00. But would need to sum all the agents which took a break/lunch between 10:30-11:00, and 11:00-11:30.

View 2 Replies View Related

Convert Times Into Hourly Intervals

Sep 26, 2013

i have a s/sheet with thousands of times in a column (eg col1 below in 24 hour format) and would like these in an adjacent column in hourly intervals (eg in Col2 below

e.g.

Col1 ColB
00:23 0 - 1
02:54 2 - 3
09:07 9 - 10
15:24 15 - 16

Is there a formula I can run to enable?

View 2 Replies View Related

Copy Columns & Paste Mutiple Times At Intervals

Oct 12, 2007

Code copies the first two columns of a many column table and pastes them at a certain interval (14 columns) to make transfer to a report easy. The problem is that the worksheets each have a different number of columns, but none more than 56 columns. The macro works wonders on the first sheet, but thereafter does not work at all. The first sheet has 27 columns, the second sheet only has 4 columns and the one after has 38 or something.

Option Explicit

Public iMaleGroup As Integer
Public iFemaleGroup As Integer
Public iMaleAnimal As Integer
Public iFemaleAnimal As Integer
Public iMaleGroup1 As Integer
Public iFemaleGroup1 As Integer
Public StudyTitle As String
Public SmallAnimal As Boolean

Sub CommandModule()

Dim Wrkst As Worksheet
Dim wsName As String
Dim wsSubject As String
Dim wsNumber As String
Dim rSummaryHeader As Range
Dim MergedHeader
Dim NextMergedHeader
Dim HeaderRange
Dim AddHeader
Dim TableHeader
Dim SumTableHeader
Dim PasteRange
Dim x As Integer
Dim n As Integer
Dim z As Integer
Dim i As Integer

Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False ............................

View 9 Replies View Related

Count Number Of Times Specific Intervals Occur Between Relevant Values

May 5, 2008

I will say exactly what i want.

I want to keep statistics of roulette.

More specifically, i want to count how many times a dozen lates to come.

The range from 1-12 is 1st dozen, from 13-24 is the 2nd and 25-36 is the 3rd.

I want to count among these numbers, how many times one of three dozens appear every 1 time, how many times appears every 2 times, .... until 20 times.

For example: I have put in a column 500 numbers from 0 to 36.

The output must be like this:

1st Dozen: Every 1 time: x
Every 2 times: n
Every 20 times: m

The same for 2nd and 3rd dozen.

View 9 Replies View Related

Macro That Runs Every Minute When System Clock Changes Minute?

Sep 13, 2013

create a Macro that runs when system clock changes minute and then at every change of minute of system clock.

For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...

I already have a Macro that runs every minute from initial run time, using

Code:
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"

but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.

View 9 Replies View Related

Interpolating Data At 5-minute Resolution Down To 1-minute Resolution

Nov 27, 2013

Entering a formula which will allow me to expand my data set by interpolating between sets of x,y values. My data set is in 5-minute resolution and I would like to interpolate so that I can output a new set of x,y values in 1-minute resolution. Here is my example of what I have and what I would like my end result to be, and also I have attached a sample of my data set in the below excel file:

Original data set
Date TimeTemperature oC
6/12/13 13:30 18.28
6/12/13 13:35 17.9
6/12/13 13:40 17.9

Desired data set
Date TimeTemperature oC
6/12/13 13:30 y
6/12/13 13:31 y
6/12/13 13:32 y
6/12/13 13:33 y
6/12/13 13:34 y
6/12/13 13:35 y
6/12/13 13:36 y
6/12/13 13:37 y
6/12/13 13:38 y
6/12/13 13:39 y
6/12/13 13:40y

datetime_temp.xlsx

View 2 Replies View Related

60 Minute Time To 100 Minute Time

Jan 8, 2008

Is there a formula that will calculate standard 60 minute time in to 100 dinute time? Example 2:21 = 2:35. I know how to get to the answer with a calculator, but would like a formula to cut out all of the extra steps.

View 3 Replies View Related

Trying To Chart Calls Per Minute / Per Day

Dec 6, 2013

I'm trying to use some call data for some predictive staffing. I need to see the highest number of calls happening around the same time each day. I'll have to do this for a number of different lines, so working out a formula that could apply.

I have two columns, date and time stamps of the calls. A small sample is below.

7/1/13 5:50 AM
7/1/13 9:49 AM
7/1/13 10:33 AM
7/1/13 10:53 AM
7/1/13 11:19 AM
7/1/13 11:41 AM
7/1/13 12:07 PM
7/1/13 3:58 PM
7/1/13 4:03 PM
7/1/13 5:35 PM
7/1/13 6:27 PM
7/1/13 6:39 PM
7/1/13 7:37 PM

View 2 Replies View Related

Formula For Minute Subtraction?

Mar 6, 2014

What is the formula for taking two times, and finding the minutes between them. E.g. 12:35 PM-12:28 PM=7. I know i can use (A1-A2)*1440, but is there an easier way?

View 4 Replies View Related

Calling Sub Routine Every Odd Minute

Dec 17, 2012

I currently have a macro that runs every 10 seconds. This macro calls 5 sub-routines.

Code:
Public Sub Copy_Data()

*****code****
*****code****
*****code****
*****code****

Call SortData

Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File

Application.OnTime Now + TimeValue("00:00:10"), "Copy_Data"
End Sub

I need to keep the current code to run Copy_Data every 10 seconds, however, i would like:

Code:

Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
To be called/run every odd minute, for example, 00:01:00, 00:03:00, 00:05:00

View 6 Replies View Related

Refresh Sheet Every Minute?

Mar 21, 2013

Is there a way to refresh a sheet every minute? So not on selection change but automatically every minute

I know this code will refresh but i dont know how to do this every minute (Not a selection change as then this could refresh every second which will not be efficient)

Sheets("Notes").Calculate

View 1 Replies View Related

Finding Page Per Minute

May 23, 2008

i am using excel 2007 on windows xp. here is what i have, i have 5 scanners that i want to track to see how much they scan per minute in a day, week, and month period. here are my data in excel for one scanner in a 8 hour shift:

500047022059574

A1 = the image they scan for a day
B1 = hours they worked that day (cell formatted to [m])
C1 = A1/B1

it should come out to 10.63 but it doesnt.

View 9 Replies View Related

How Do I Make Macro Run Every Minute

Nov 4, 2008

I have a macro called MyMacro which I would like to run every minute starting from 08:00:00 and finish at 14:00:00.

View 9 Replies View Related

How To Group Values Related To Each Minute

Apr 16, 2014

I have a table in format below, I want show a third column with sum of w.r.t the minute, so for time 2:10 ,

i want to show 10 which is sum of first four values.

Started DB request
2:10:00 2
2:10:11 3
2:10:11 4
2:10:13 1
2:11:10 3
2:11:11 2
2:11:12 1
2:12:10 1
2:12:12 2
2:12:12 2
2:12:13 1
2:13:11 1
2:13:12 1

View 4 Replies View Related

Losing A Minute When Subtracting Time?

Jan 6, 2014

In cell L7 I have (80/60)/24 and formatted h:mm to get 1:20. In cell M11 I have 1:00 (h:mm). In cell M12 I have L7-M11 and get the result 0:19. Why I'm losing a minute and not getting the result 0:20?

View 5 Replies View Related

Add Hour/minute To Time On Other Cell

Jan 29, 2009

I am creating a Little League Schedule and want to start Saturday games and have the lower cells ad 2hr 45min to whatever time I post in the upper cell

View 4 Replies View Related

Round Time Value Like 08:23:51 To Nearest 15 Minute Like 08:30:00?

Jun 21, 2012

How could round a time value like 08:23:51 to the nearest 15 minute like 08:30:00?

View 2 Replies View Related

Active Clock That Updates By The Minute

Feb 4, 2007

Is there anyway of getting an "Active Clock" in a cell.
I know the =now() thing, but this is not what I am looking for,
I would like to get a clock that updates by the minute, without having to select a cell, or make excel do a calculation.

View 9 Replies View Related

Time And Cost Calculations Per Minute

Oct 17, 2007

I have costs per minute and call times in seconds

How do I work out the cost per call in Excel?

It's probably something very simple, but I just can't work it out. No matter what I try, I can't get it right

View 9 Replies View Related

Looping Through Daily One Minute Data

Mar 2, 2008

I'm trying to create a macro to loop through daily one minute data.I believe the flowchart would be something like:

Create Variable

For each day in recordset

Loop through each minute record

Run system rules

Copy to Seperate worksheet

End

Additional Info:

Data is in columns B-G (Date,Time,Open,High,Low, Close)

Sample system could be something like:

If Current record close price is > Past 2 records Close Price Then
Buy 100 Shares
Liquidate if poistion moves against by 10%
Take Profit if position increases by 5%
Else close by days end

View 9 Replies View Related

Hourly Average With 15 Minute Data

Nov 6, 2009

I have a large sheet with the values of power from a counter in a 15 minute base.

A B
01.01.09 00:15 0,25
01.01.09 00:30 1,15
01.01.09 00:45 0,75
01.01.09 01:00 2,01
and son on until the end of the month
What I am trying to do is to create a macro which calculates the average per hour and put in another columns like this:
01.01.09 01:00 average from 00:15 until 01:00
01.01.09 02:00 average from 01:15 until 02:00
and so on until the end of the month.

View 9 Replies View Related

Round A Time Entry To The Next 30 Minute Increment

Feb 2, 2006

I have a worksheet that I enter time values into individual cells:

A1 = 6:00
B1 = 12:00
C1 = 12:45
D1 = 17:15

When I enter these values, I would like some of the values to round to the
next higher "30 minute increment" such as Cell C1 should become 13:00.

I would also like some of the values to round back to the next lower "30
minute increment" such as Cell D1 should become 17:00.

View 12 Replies View Related

Automatic Colons For Hour:minute Entries

Mar 12, 2007

how to format a cell, or what formula to use, so that when an entry is made the colon is automatically put in? so that 1011 shows as 10:11

View 9 Replies View Related

Time Clock Six Minute Rounding Formula

Apr 27, 2007

I am trying to figure out the written formula for needing this answer:

For a time clock with 6 minute rounding.

If I type in 8:20 as the arrival time, I need it to round to 8:18, so that the final time will be in tenths.

Here is the rounding:

Actual Rounded Minutes in Time Clock
:57, :58, :59, :00, :01, :02
:03, :04, :05, :06, :07, :08
:09, :10, :11, :12, :13, :14
:15, :16, :17, :18, :19, :20
:21, :22, :23, :24, :25, :26
:27, :28, :29, :30, :31, :32
:33, :34, :35, :36, :37, :38
:39, :40, :41, :42, :43, :44
:45, :46, :47, :48, :49, :50
:51, :52, :53, :54, :55, :56

Anything in the same line will round to the bolded time.

Example: I arrive to work at 8:09 and go to lunch at 11:59, it will round to 8:12 and 12:00, making my time worked 3:48 or 3.80. Then I get back from lunch at 12:59 and leave at 17:14, which will round to 13:00 and 17:12, making my time worked 4:12 or 4.2 for the 2nd 1/2 of the day and 8:00 or 8.0 hours for the day.

Time In----------Time Out----------Total Time----------Decimal Time----------Total Hours
...8:12.................12:00..................3:48......................3.80
..13:00................17:12..................4:12......................4.20..........................8.00

The problem is I want to write the actual time and not have to round everything myself and yet I need the decimal time to match what my time clock actually calculates from.

My current formula for the decimal shown above as 3.80 is this: =(HOUR(F7)*60+MINUTE(F7))/60
My current formula for the total time for the day (regular hours) is this: =IF((((E7-D7)+(I7-H7))*24)>8,8,((E7-D7)+(I7-H7))*24)
My current formula for overtime is this: =IF(((E7-D7)+(I7-H7))*24>8,((E7-D7)+(I7-H7))*24-8,0)

This is not a time clock, it is my personal copy of what my time for the week was/is, so that I can compare it to the actual time card that I have to sign off on.

View 9 Replies View Related

Simple Copy And Update Cell Value Every Minute

Mar 21, 2009

I am trying to grab the value of a cell every time it updates and copy that value and store in a new cell.

So I have a cell (A2) and it updates every 60 seconds, I need that value for t=1 (first time) to be copy and pasted into a new cell (B2). Then when t=2 (second time, 60 seconds later) I need the value to be pasted into the same column but a new row, namely cell C2. Then when t=3 (third time), I need it to be copied to cell D2 and so on.

I'm assuming it just a macro that is called every 60 seconds and grabs the value of A2, but then needs to find the next cell in column B that is empty and paste it there.

View 9 Replies View Related

Rounding Time Value To Nearest 6 Minute Increment

Mar 16, 2007

What formula could I use to round up or down time values to the nearest 6 minute increment?

Example:

Time ----> Rounded To
5:02 PM --> 5:00 PM
5:03 PM --> 5:06 PM
4:18 PM --> 4:18 PM
4:19 PM --> 4:18 PM
4:20 PM --> 4:18 PM
4:21 PM --> 4:24 PM
etc.

View 6 Replies View Related

Day Hour Minute Format To Total Hours

Aug 28, 2007

We have a system called Datamart that outputs in excel formatted file.

The output of a duration is : 22.00:8.00:25.00 ( day hour minutes )

I want to be able to add, subtract, average, calculate the 10 fastest/slowests durations from a list of durations in this same format. I have googled and tried custom formatting but excel does not like this format. when I try to sum a range and divide by the number excel gives me 0.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved