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?
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.
I am a flight instructor and legally we cannot work more than 8 hours in any consecutive 24 hour period. I'm trying to create a spreadsheet that will calculate the totals from that 24 hour period for me.
Right now I have something that sort of works, but not the way I would like it. I have a column for "time" that has the date and ending time of the flight and I am using that as if the total flight occurred at one moment (the ending time).
So, for instance let's say I did these flights
1: 8AM-10AM (2 hours, clocked at 10AM)
2: 11AM - 2 PM (2 hours, clocked at 2PM)
3: 7AM-9AM the following day (2 hours, clocked at 9AM)
If I have my formula calculate the time for 9AM the following day (totaling the past 24 hours) the first flight won't be included in the calculation since the hours from that flight is only imputed at 10AM. The formula would read 4 hours rather than the (actual) 5 hours.
Here is my workbook : 8 hour calculator sample.xlsx
Here are the formulas I am using
The time is formulated at date and time, with time being the ending flight time
The start time for the calculation is
[Code]....
end time is just =NOW()
Total calculation is
[Code] .....
All I'm trying to do is use the totals under "flight start" and "flight end" instead of the end flight time I had to put in under the date column.
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?
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.
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] ...........
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)
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.
So I have an Excel Table that consists of data from a Ping utility (we're having a lot of connection issues on our DSL line), every 5-10 seconds the program pings various websites like google, yahoo, etc. and logs the ping time.
I'd like to make a chart or graph that shows the average ping per Hour (or 30 minutes, or whatever) for all these sites combined, basically take an hour of pings, find the average of all or just the pings from one IP, plot.
Table is
ColA = Date Time (8/20/2013 15:57)
ColB = IP Address (173.252.110.27)
ColC = Ping (123)
ColD = Result (Succeeded/Failed)
ColD = DNS (facebook.com, it's a vlookup from a table of IPs and their DNS address)
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
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.
View 9 Replies View RelatedI 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!
View 9 Replies View RelatedHow do I paste the first chart into the second chart but maintain the format of the second chart?
View 2 Replies View Relatedi 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.
View 2 Replies View RelatedI need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.
I have tried using array formula
{=MATCH(E1,IF(A2:A21>=E2,IF(A2:A21=E2,IF(A2:A21
I have this
Select Case Hour(Format(now, "13:00"))
Case Is >= 7, Is = 15, Is = 23, Is
I have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength))
FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.
how i would calculate the number of sickness days and occaisions in the previous 26 week period from todays date.
I've attached the spreadsheet.
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
I have a work book that has about 1200 entries. I'd like to grab everything that comes after the last comma or the last period in the cell. Can this be done?
View 4 Replies View RelatedI have a sheet i am working on at the moment, i basically have in Column A (named Interview Date) a date and in column Q (named Date Cleared) another date.
What i want is so when i enter a date in column Q if the date is 14 days or more after the date in column A it will highlight Blue and if the date is upto 14 days after the date in Column A it will highlght green.
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?
How can I find the total of interest (or and expense) during a given calendar range
Data is as follows
Date Type Amount
4/1/11 Interest 4.00
4/15/11 charge 22.00
5/1/11 interest 3.00
I want a cell to compute the interest in april only.
I have tried =sumif(range, end date, amount_range)- sumif(range, beg date, amount_range)
but how do I nest the type argument into the argument
Possible approaches advanced filter? and function? add another column that uses an if statement to do interest only and do the sumif statement above I was hoping to have a drop down box that could have interest, COGS, beside it and april, may, june above it
I am trying to get periods added to initials.
Colum A
(Can be up to 4 caracters)
FJM
ML
B
GHTJ
What I need in colum B
F.J.M.
M.L.
B.
G.H.T.J.
What formula can I use in the B cells to get this result?
I'm trying to do a very simple web query drawing a table of holidays based on what country you select.
I'm using this very basic parameter setting
WEB
1
http://["Web Address"]
Selection=EntirePage
Formatting=All
[Code]....
I set up the connection and when I add the country (using a separate cell and a concatenate) the URL looks ok in the cell (www.timeanddate.com/holidays/Ireland) but the query returns an error saying it it's unable to open "www.timeanddate.com/holidays/Ireland."
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).
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.
View 2 Replies View RelatedI 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.
I have a code that I have put in the ThisWorkbook section, and I thought it should work - but nothing happens....
Code:
Sub FirstSub()
Application.OnTime TimeValue(Sheets("SetUp").Range("G1").Text), "SecondSub"
ResetTime = Now() + 1
Application.OnTime ResetTime, "FirstSub"
End Sub
Sub SecondSub()
If Weekday(Now()) > 2 And Weekday(Now()) < 7 Then Call CreatePDF
End Sub
It's supposed to call a sub called CreatePDF.
I have a table on excel that I would like to have an event calculated by the hour and would like to know how to, ex:
1900
2000
2100
2300
00-0100
0
1
0
2
0
that above is where I want the formula to calculate the following:
Activiy 1
2015
Activity 2
2310
Activity 3
2348
Also I would like to do something similar like that but for age, ex:
18-24
3
25-40
1
41-59
0
[code].....
I do not understand how to get a macro to run exactly on the hour, each hour of a day, day after day?
View 9 Replies View Related