# 1/2 Hour Intervals To 15 Minutes

Sep 26, 2008

08:00 26calls
12:00 50calls
16:30 16calls

The problem is the data is output as above just outputing intervals with data.

I need to convert this data to 15 minute intervals as below:

08:00 13calls
08:15 13calls
12:00 25calls
12:30 25calls
16:30 8calls
16:45 8calls

## Varying Price Points Based On Hour Intervals

Jan 18, 2014

I have a client where I have agreed to a variable price per hour depending on how many hours is used.

So the first 40 hours of a month costs X USD
The next 40 hours of a month costs Y USD
The next 40 hours of a month costs Z USD
The next 40 hours of a month costs Q USD
And all hours above 160 in a month costs T USD

All hours are registered as decimal with 15 minutes as a minimum, so 0.25, 0.5, 0.75, 1, 1.25 etc.

Now the challenge for me is to set up a formula calculating the total cost based on how many hours is registered each month.

I.e. if I register 46 hours in one month the first 40 hours should be multiplied by X and the last 6 hours by Y. And if I register 173 the calculation looks like this:

40 * X
40 * Y
40 * Z
40 * Q
33 * T

And I can't figure out how to make the formula calculate based on what is between the various intervals.

## Return Time Intervals Based On X Minutes

Feb 20, 2008

I have a spreadsheet set up that has a series of timestamps and values. Please find a copy attached. I need to set up a macro that will allocate the timestamp in column C to an interval of time, e.g between 12:30 - 13:00.

To achieve this I tried to set the following code up in VBA, which would input a letter in a blank cell based on the time interval that the timestampin column C falls into, for example if the timestamp is > 12:30 and < 13:00 then "A"...the plan was to extend this concept to accomodate all of possible the time intervals I have.

Sub time_intervals()

Dim x As Integer
Dim lastrow As Long
Dim timeA As Date
Dim timeB As Date
Dim timeC As Date
Dim timeD As Date
Dim timeE As Date

The idea was that the timemaster value would be the timestamp in column C of my spreadsheet and the timeA,timeB,timeC etc would reference the timeintervals. However this does not seem to work as the value returned by my if statement is always incorrect - for example timemaster in cell C2 is less than timeA but the if statement always returns FALSE as opposed to "A"

## Formula Hours And Minutes To 1 / 4 Hour?

Mar 1, 2012

I have a program that outputs a total amount of time that a service was provided. This time is reported in hours minutes [Example: 01.08 (hh.mm)]. I need a formula that will "round" to the closest .25 based on a 7 minute window on either side. Here how it would need to work....

0 - 7 Minutes = .00 or 1.00 - 1.07 = 1.00
8 - 22 Minutes = .25 or 1.08 - 1.22 = 1.25
23 - 37 Minutes = .50 or 1.23 - 1.37 = 1.50
28 - 52 Minutes = .75 or 1.28 - 1.52 = 1.75
53 - 7 Minutes = .00 or 1.53 - 2.07 = 2.00

## Multiply Number Per Hour Or X Minutes

Jun 1, 2008

I am wanting to take a number (any number) and multiply it by per minute using real time. For example: Say I have 12 apples. I want that 12 apples to multiply per hour.

12x(per 60 minutes)= total
The total will change every hour because the formula will be using real time. I can write the formula for 12x60 but to enter the time, based on real time I just don't know or even if it can be done.

## Calculate Number Of Minutes In Each Hour Of Breakdown

Jan 7, 2014

I am trying to calculate the the breakdown time for each hour in the factory. I have the start time and finish time of each breakdown. There are 8 hours in a shift starting at 6:45. So hour 1 would be (6:45-7:45), hour 2 (7:45-8:45). If the machine breaks down between 7:06 and 8:20 what will be the amount of time lost in each hour? In this example it would be 39 mins in hour 1 and 35 mins in hours 2. How I can achieve this result?

## Display Day, Month, Hour & Minutes In Seperate Labels On A Userform

May 28, 2008

I have made a form with a calender control, a keypad for hours and minutes. I have no problem getting the date from the active cell into a label caption and changing it by the calender, but I am having trouble reading just the hour and minutes into separate label captions from the active cell and changing them via the keypad and updating the active cell at the same time with a new date and time.

## Formula To Calculate Time Allotted Minus Time Used And Show Difference In Hour And Minutes?

Apr 27, 2014

Formula to calculate time allotted minus time used and show the difference in hour and minute.

## Changing Date/time To Run A 12 Hour Production Schedule, And Not 24 Hour

Sep 4, 2006

I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).

The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.

The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.

My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.

Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.

Of course if demand exceeds the allotted time we put on overtime.

Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?

And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.

I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.

## Excel 2010 :: Counting Users Per Hour For Each Hour

Oct 21, 2013

I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.

I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.

For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.

Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?

A
B
C
D
E

1
2
4
2

2
In
Out
8 am
9 am
10 am

[Code] ...........

## Formula To Convert 24 Hour Day To 8 Hour (working) Day?

Nov 12, 2013

Is it possible to take data in format dd:hh:mm and convert to working days - ie. 8 hour days?

For example:

02:12:15 (60 hours and 15 minutes .. or 3615 minutes)

to be converted to:

07:04:15 (7 working days: 4 hours: 15 minutes)

## Distribute Hours To A New Table Hour By Hour?

Dec 2, 2013

I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.

Attached file, defined example and detail.

## Convert Days:Hours:Minutes:seconds To Minutes?

May 25, 2011

I have a spread sheet with a colum showing average time to complete a task. This is currently shown as Days:Hours:Minutes:Seconds (4:19:33:19). I meed it to be shown purely as minutes, or at least as hours and minutes.

## Convert Hours To Minutes. Display Total Minutes

Dec 5, 2006

I have a formula which will calculate the number of hours and minutes between two military times. I would like it to calculate the total number of minutes instead of hours and minutes. I have uploaded a small example of what i have so far.

## Convert Decimal Hours And Minutes To Minutes

Jul 4, 2006

What formula will convert 4.50 to 530 minutes ( "Decimal Time" )

another example 16.50 to 1250 minutes.

## Convert 3786 Minutes To Day:hours:minutes

Jan 21, 2009

I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute

## Converting Minutes Into Hours And Minutes Using A Formula

Aug 22, 2006

creating a formula for converting time data that has been created in an excel spreadsheet in minutes i.e. 516 minutes which I need to turn into Hours and Minutes i.e. 08:36 I am not experienced using Formulas, apologies if this question has been posted before, I did use the search facility to look for threads, but could not find anything related

## Convert Hours & Minutes To Minutes `

Jul 4, 2007

I have a worksheet which I am trying to format as a template which includes inputting start times and end times of work and calculating how many minutes are taken to do the job. I just can seem to find the correct formula.

## Round To Nearest 15 Then Nearest Hour If Less Than 1 Hour

Apr 4, 2014

I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.

I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.

Attached is my form : Timesheet Form 2014.xlsx

## Convert To 12-hour Time From 24-hour Time

Mar 7, 2008

I'd like to convert from 24 hour standard time to 12 hour time using VBA code. For example: instead of 13:00 I need 1:00.

## CONVERT 24-Hour Time To 12-Hour Time

May 8, 2008

I have a list of FLIGHT departure times that are listed in MIL TIME, however, there is no : in the format. Its just 4 or 3 digit numbers. I need to convert these to time in 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!

## Transpose One Row With Intervals

Mar 21, 2009

I am trying to write a macro for transposing one row into multiple columns where the starting point for each column will be 15 cells starting from B4. I want to replicate the transpose for 200 rows.

## Change The Intervals

Jul 9, 2008

How do i change my data file from 1' intervals to 5' intervals?

The way it is now The way I need it
8300 8300
8301 8305
8302 8310
8303
8304
8305
8306
8307
8308
8309
8310
thru 11,000

## Getting Date At Specified Intervals?

Feb 12, 2013

My sheet consists of the following Data in the table ( to be increased to 99 rows or events )

I need to find the Next Date Occurence till the End_date

Next_Date1 = Start_date + Interval
Next_Date2 = Next_date1 + Interval

and so on till Next_Datexx is greater than End_date

I need a VBA code to fill the adjacent columns ie Next_date1 to Next_Date99 ( till the end_date), only if the Status is "DONE". If the Status is blank then the relevant colums must not fill.

I can do it with formulas, but need the VBA code.

Also the number of rows can be increased to 99 rows

Event
Start_date
End_date
Interval

[Code].....

## Histogram: Intervals Below Bar

Oct 5, 2006

I'm trying to make a histogram with the data analysis tool. The problem is that whenever I make a chart it doesnt show my intervals correctly. It puts them under the bars instead of between them.

## Histogram Intervals

Oct 6, 2006

I'm making use of a histogram to visualize my data. The only problem is that 90% of the data has a value which lies close to eachother but 10% is very different.

For example:

Interval-----Frequency
0-10------5
10-20-----7
20-30-----6
30-40-----0
40-50-----0
50-60-----0
60-70-----0
70-80-----0
80-90-----0
90-100----0
100-110---2

Now the histogram I will obtain won't look very good. Is there a way I can have an interval like 40 - ... which resembles everything above 40?

## 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.

## Identify All Consecutive Intervals

Sep 18, 2013

Is there a formula that can identify all the consecutive intervals that have value below 90%.

## VBA To Clear Contents At Specified Intervals

Mar 6, 2014

I think I've had enough of using excel generated macro's as an excuse for decent code. I need to turn this:

Small procedure, function, what have you that will continue this pattern until I hit some kind of indicator to tell it to stop. This just clears the contents from 7 rows, hops down 9 rows and does it again.

## Rearranging Columns With Intervals

Aug 16, 2009

I have posted earlier thread on similar problem but i did not get much response.I am really having a tough time to record a macro, where my columns will be selected with an interval and then will be pasted in to another sheet....