Tracker
HOME    TRACKER    Excel

# Chart Over A 24 Hour Period

## I'm attempting to chart 3 series over a 24 hour period (8am-8am). The 3 series are captured in 1 minute intervals. My X axis intervals is displayed hourly though. My issue is, charting goes bad at 00:00:00. i.e. it stops. Here are the values I have on my X axis min: .33333 max: 1.35 maj: .04167 min: .00347 cross at: .33333 Any ideas how I can get from 8am to 8am?

Related Forum Messages:
Calculating Time Across A 24 Hour Period
Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.

Example

In B5 Startime is 22:00
In C5 Finishtime is 06:30

In I3 increment begins at 00:00
In I4 increment ends at 00:30

The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.

Chart A Graph Of Jobs Over A Period Of Time
I have an Access database, with a table of jobs processed.

One column for Job ID (Unique), Date it was processed, By Who it was processed and Comments ascoiated.

I need to chart a graph of Jobs over a period of time. How many jobs done per date. In excel preferebly, I have programs like Database Plus for excel. And I am familiar with VBA. I can't figure out how to uniquely sort how many jobs for each date. As there are numerous job entries for on the same dates. I think it can be done with an SQL query or sorting it with VBA somehow. Any help will be greatly appreciated. Or can anyone suggest an application designed for charting with Access databases in Excel?

Changing Date/time To Run A 12 Hour Production Schedule, And Not 24 Hour
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.

CONVERT 24-Hour Time To 12-Hour Time
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!

Convert To 12-hour Time From 24-hour Time
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.

Hour (now)
I have this

Select Case Hour(Format(now, "13:00"))
Case Is >= 7, Is = 15, Is = 23, Is

Percentage Increase In A Period
I'm doing research with a professor at the University of Florida. This is my first time using Excel to any extent, so I need everything from the ground up!

I have the housing values for each of the 50 states from 1975 to 2006. I need to analyze each state individually to see if there were any points during that 26 year span when housing values increased by more than 20%. I need to know when this increase started and when values peaked and began to fall. I could do this manually, but I'm assuming that would take a while.

Is there a simple way to do this in excel? A macro, or a UDF (I have no clue about either of them by the way).

The 50 states are listed in a column and the rows next to each state having housing values for the 26 years. ( I don't know if that is relevant).

Calculating If A Period Is During A Workday
I have two dates in this format:

start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00

i need to check if the period in between those is during a workday.

our workday starts at 10:00:00 and finishes at 19:00:00

so my expected result is 3 hours. (1400-1100)

other example:

start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00

result is 1900-1100 + 1100-1000

= 9 hours

because it is during two workdays...

is there a way to calculate this in VBA?

Calculate Absence In A Period
how i would calculate the number of sickness days and occaisions in the previous 26 week period from todays date.

So in C4 i want to have the total number of times(occasions) the person has been absent in the previous 26 weeks.

and in E4 i want to have the Total number of Days (taken from E7:E20) the person has been sick in the past 26 weeks

Sharing The Value Of Job Over A Certain Period
i need to share the valve of the job over the months. see attahed excel sheet. say the job worth 10k has a start date of the 30th of nov and the end date is the 1st of dec. the value of the job is shared between two days and then put into the relevent cell. as regards to the months they are not calander months so the start date and end date of the months are diffrent see attached sheet for full details.

Period Replaced By Colon
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.

Change To Hour
How do I change a column of times from "h:mmAM" to just "h"?

24 Hour Timesheet
I am having trouble with this formula

=IF(E4-D4 < 1/24*7.1,E4-D4,E4-D4-1/24)*24

it works well unless the staff member works past midnight. I get a negative hours worked value returned.

for eg

E4=8AM and D4 is 5PM i get an answer of 9 hours in F4, this is all good but if the start time E4=4PM and the finish is D4=1AM then I get the result of -15 hours in F4

Time Trial Period In My Spreadsheet
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times.

I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between \$20 and \$60....

Work Out The Value Of Cars Over A Period Of Time
I have a project where I have to work out the value of cars over a period of time.

The cars depreciate at 36.9% per year over a 5 year period and I can't work out for the life of me how to create a formula in excel that give me the values at the end of each year.

Each car costs £10000 and I need to know what the value of each car is at the end of each year

Converting Time Period In Hours
How do I convert 7.30 hours into 7.21 (ie 7 hours 21min.) Note I do not wish
to use the standard hour:minute formatting.

Graphing - Selecting Specified Time Period
I have sheet that is populated with data daily. I wish to create graphs from information contained within a table in this sheet where i can select the start date and end date of the graph possibly from a drop down menu. I will be creating multiple graphs and wish for them all to change when i select the time period.

Sumif If The Column Is In The Correct Period
I have a set of costs in column c (more that shown below) that have an indicator - for example 0 or 30 (there are more).

I need to populate rows 18 and 19 with the sumif - i have other tables where it is a straighforward sumif as the periods align, here though I need to check that the periods (rows 4 & 16) are checked and then the right data entered underneath.

I can't find a way to combine the sumif, with an if statement checking that the period is correct too? ...

Read Data Only To Current Period
I'm trying to total data only up to the current period so I presume the answer lies in a =sum and index but I can't get it to work. The attached worksheet explains it better.

YTD And Period Sales Report
I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format.

The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only).

For the current Year- period (month) and YTD (only up to the period displayed).

For the last year- period and YTD (only up to the period displayed).

The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales

I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck.

I would also like to be able to select which period I am viewing but this is secondary.

I can upload an example if necessary.

Report On Date Period And Subtotal
I have attached a sample of my spreadsheet to use as a reference. I suggest you open that up and give it a look through to get an idea of what's going on.

What I'm looking for is a way to create a report (the quickest and easiest way of course, as always) which will give the total for the columns 'Exit Weight', 'Net Weight' and 'Entry Weight' sorted by each company between the periods of the 25th of each month to the 24th of the next month (the Dates are in Column A obviously)

Below is an example of the output I am looking for:

Company: Period: Exit Weight: Net Weight: Entry Weight:
EASTWELL HA.. 25/10/07-24/11/07 102.2 124.3 200.14
25/11/07-24/12/07 143.7 185.2 250.18
25/12/07-24/1/08 ... ... ...
BOURKE TRANS.. 25/10/07-.... ... ... ...

the totals for the Exit Weight, Net Weight and Entry Weight are not the actual totals, they were just put in to give you an idea of what I'm talking about.

And on and on. If there is a way to automate this with Excel VBA and some sort of code that you could give me to plug into my spreadsheet I would be very appreciative. I understand this is a bit complicated and would probably require a few steps so if don't get the answer I'm looking for I'll probably find a way to split it into steps and then ask the questions seperately.

Finding The Quarter Period Of A Date
I'm trying to find out the date quarter of a given date in cell A1.ie Q1,Q2 etc.
I've found the code below to work out fiscal quarters.I thought if I dropped the +1 from the end of the formula it would work out the quarter in non fiscal terms. This however only works from months Jan to Nov. December returns a zero.

=MOD(INT(MONTH(A1)/4)+1,4)+1

1/2 Hour Intervals To 15 Minutes
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

24 Hour Double Time
I have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.

Counting Entries Per Hour
My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).

How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?

If Statement For Hour And Min
how to change the below statement to allow for when the time in B9 is 1:01 so it displays as 1 Hr 1 Min. I tried an IF statement to change the hr but i cant seem to find a way to change the Mins at the same time.

HOUR(b9)&" Hrs "&MINUTE(b9)&" Mins

Calculating Episodes Per Hour
I need to calculate the number of calls my operators take per hour of the day in order to calculate the busiest times of the day. Each call is time stamped and captured but now how do I calculate the total number of calls taken between 07:00:00 and 07:59:59 etc etc for the whole 24 hour period?

Converting Digit To Hour
I have simple (or not) question:
how can I convert a digit that I input into a cell to an hour format ?

I want to achieve something like this:
- when I input a digit into a cell , for example: 9 a want to convert it to 9:00 (9 hours, 0 minutes). How can I do it ?

I have a start time in cell A1 (say 9am entered as 900), cell B1 has a time interval (25min), Cell C1 gives total (925). Cell B2 has the next time interval (56min). How do I get cell C2 to give total of 1021 rather than 981? Values in columns B and C continue on down.

Automatically Show Every Hour
What other code than that shown below do i need to make my message box work,

"Module Code"
Sub MyMacro()

Application .OnTime TimeValue("00:00:00"), "MyMacro", ["23:00:00],
[TimeValue "01:00:00"=True]
MsgBox(BoMLogDue, vbOKOnly, BoMLogDue) As VbMsgBoxStyle

End Sub

"Workbook Code"
Private Sub Workbook_Open()
Application.OnTime TimeValue("00:00:00"), "MyMacro", ["23:00:00],
[TimeValue "01:00:00"=True]
End Sub

Run Macro Every Hour
I have a macro I need to run every hour. I have tried 3 different macros that seem to work the first few times but then the code executes and will run 2 or more times.

Application.OnTime ("01:00:01"), "macro1"

Application.OnTime Date+TimeValue("07:05:00"), "macro1"

I then tried this http://www.cpearson.com/excel/OnTime.aspx

How To Get The (average Or Sum) In A Limited Period Of Time
This code help me in typing the date and time in any cell of column (B) automatically just in case I enter somthing in any adjacent cell of column (A)

Automatically Close UserForm After Time Period
I have the following code in a simple userform containing only a label:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub UserForm_Activate()
DoEvents
Sleep 4000
End Sub

When I invoke this as a single macro in a blank workbook and immediately change focus by pressing ALT+TAB to enter NotePad the userform is shown and exits normally after 4 seconds.

However, when I issue this code from within a larger macro, and hit ALT+TAB to Notepad, the excel application hangs on the displayed userform until I click back into Excel.

If required, I can happily send you the code as an attachment.

Completing Probation Period This Week From Join_Date
I have got Names in column A and Join_Date in column B. In column C I want those whose Probation period is going to finish this week. Probation is of 90 days.

Calculating The Number Of Business Days In A Specified Period
It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g

Mondays, Wednesdays and Fridays of June
June 1, 2006
June 3, 2006
June 6, 2006

Tuesdays, Thursdays and Saturdays of June
June 2, 2006
June 5, 2006
June 7, 2006

This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months

Check If Final Week Of Fiscal Period
I am attempting to check to see if the current week is the end of a period or not. If it is, I need to display some text, and if it is not, I need to display nothing.

Since I am Computer Science major, the best way I can describe this is through and if structure from a low-level programming lanuage (like C++ or Java):

Removing Chars Not (a-z,1-9,space,comma,period)
I have a file of names that has some undisplayable characters. I am trying to match against a different file. It works if I overtype the "blank" fields with a space. How can remove these trailing problem characters whatever they are? I tried CLEAN and TRIM and the mystery characters are still there.

Sumproduct: Count Number Of Leaves Within A Specified Period
=SUMPRODUCT(SLVL!\$B\$6:\$B\$49>=\$A\$2)*(SLVL!\$B\$6:\$B\$49<=\$B\$2)*(SLVL!\$A\$6:\$A\$49=A12)

I'm sure that there had been an error somewhere but I can't figure it out. I'm getting zero value. Situation: need to count number of leaves within a specified period, thus I opted to use Sumproduct.

Determining Number Of Specific Days In Period
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.

Calculate A Pay Back Period For A Certain Investment
I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.

Title Of A Document Contains A Period And Is Stopping The Code
I have a two documents whose names contain periods out of 70. Example is St._Paul and St._Louis_Park. When I run through the code it stops it with the error that the name cannot be found. My list looks like this..

Club Name
Eagan
Apple_Valley
St._Paul
etc.

When I remove the period it works. Is there a way to make it work with the period in there so I don't have to change a bunch of files and documents? Here is the code...

Sub OpenUp()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks(ThisWorkbook.Name)
Set ws = wb.Worksheets(1)
With ws
TotalRow = .Cells(1, 255).End(xlDown).Row
Finalrow = .Cells(TotalRow, 1).End(xlUp).Row
End With
For i = 2 To Finalrow
clb = ws.Cells(i, 2)
psw = ws.Cells(i, 3)
psw2 = ws.Cells(i, 4)
mnth = ws.Cells(i, 5).........................

Extract Text Before/After Period (Full Stop)
I have a series of text - domains actually (i.e. goldintre.cr.usgs.gov.) - and I need to extract the text that appears before the first period in one cell, and the text that appears after the first period. Both have to be without the periods. So in the above example the result should be 'goldintre' in one cell and 'cs.usgs.gov' on the other.

Working With Time: Blocks Every Whole Hour
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.

On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.

Count Time (entries Per Hour)
I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time)

For 12:00:00 all times would be between and including 12:00:00 and 12:59:59

Column B | Count
------------------
12:00:00 344
13:00:00 44
14:00:00 5

Rounding Timesheet To Quarter Hour
I have made a time sheet and am trying to have the total hours and grand total- round up to the nearest quarter hour, I.E. (.25, .50, .75. 00), if anyone can help me please it will greatly be appreciated, this is what i have now, in my totals fields:

ROUND(IF((OR(B13="",C13="")),0,IF((C13<B13),((C13-B13)*24)+24,(C13-B13)*G1424))+IF((OR(E13="",F13="")),0,IF((F13<E13),((F13-E13)*24)+24,(F13-E13)*24)),2)

I Have also attached the file so you can see it completely.

How To Get A Macro To Run Exactly On The Hour And Day
I do not understand how to get a macro to run exactly on the hour, each hour of a day, day after day?

Weighted Average To Come Up With A Speed Value For Each Hour
I have a spreadsheet with the following data that elapses an entire year, broken down into 24 hourly sections per day:

column A: hour of the day (from 0 to 23)
column B: vehicle group (separated into "Passenger", "SingleUnit", and "TractorTrailer")
column C: average speed for that vehicle group for the corresponding hour
column D: total volume/number of vehicles in that vehicle group for the corresponding hour

I would like to apply a weighted average to come up with a speed value for each hour (i.e. combine the average speeds for each of the 3 vehicle groups based on their relative volumes across the entire dataset).

My final table should show only:
Hour (from 0-23), Avg. Speed (containing the weighted averages from the dataset)

Attached is a screenshot example.

Rounding To The Neartest Quarter Hour
I have searched around and have found several formulas designed to round to the nearest 15 min interval, but none seem to be working for me. If anyone has some insight.
A1 A2
R1 Main Task Total in Hours
R2 Sub Tasking 1 30 min
R3 Sub Tasking 2 6 min
R4 Sub Tasking 3 5 min
R5 Sub Tasking 4 23 min

What I need to happen is for the total of the time allotted for each tasking to be added and rounded to the nearest 15 min interval in a hour.min format. For instance, the above would be rounded to 1.25

71 mins total, rounded to 1.25 because .25 is one quarter of an hour in decimal form. I need it to be this way to be able to multiply that by a charge amount for a total cost for the Main Task. The numbers in A2 will all be whole integers.

Convert Hh:mm.* To 12 Hour Time Format
I have a ton of time in the above format (hh:mm.*) that needs to be converted to a 12 hour format (2:15 PM). I'm not wonderful with formulas, but if it's explained "simplistic" enough, I can probably get it. Please help - I've googled this to death every possible way. I can go in an manually change 19:37.5 to 7:37 pm - but it will take me FOREVER!

How Do I Round To Nearest Quarter Hour
I need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour.