Working With Time: Blocks Every Whole Hour
Aug 1, 2007
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.
View 9 Replies
ADVERTISEMENT
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)
View 3 Replies
View Related
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.
View 9 Replies
View Related
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.
View 9 Replies
View Related
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!
View 9 Replies
View Related
Apr 27, 2014
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies
View Related
Oct 1, 2009
I'm using Excel to schedule employees shifts at my restaurant. Column A is their name, Column B is their start time, Column C is their end time. I'd like to be able to count the number of employees working during any given hour of the day to assure I've got enough staffing each hour. I'm thinking it will require a COUNTIF function but I can't figure out how to make it work. I've attached a sample sheet.
View 4 Replies
View Related
Apr 11, 2009
I am trying to calculate the amount of time in blocks, using a pre-determined spread. For example:
AM block is anytime between 0600-1230
PM block is anytime between 1230-1830
Evening block is anytime between 1830-0000 and the Overnight block is anytime between 0000-0600
Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though:
If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5.
If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5.
If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks.
I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.
View 9 Replies
View Related
Sep 11, 2013
In A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.
View 1 Replies
View Related
Aug 19, 2014
I am trying to count the number of extra people scheduled for each hour of the day, from 2 dropdowns in A3:B7
The formula in (1) D11:D26 works, but when the shift passes over the midnight it will not count accurately.
The formula in (2) E11:E26 also works well, but only counts the number of times a particular hour is mentioned, but not the number of extras in C3:C7.
The results in (3) F11: F26 are what I need, which is based on looking at the times from the dropdowns and using the number of extras in C3:C7.
View 2 Replies
View Related
Dec 20, 2007
I need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers.
For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).
View 9 Replies
View Related
Mar 13, 2008
I found a formula for calculating time in the HH:MM AM/PM
=IF(B1<A1,B1+1,B1)-A1
This formula was to give me total hours in the cell for which it is entered, and cell format for the formula was in military format.
I cannot find this post. The formula worked in OpenOffice Calc program, but when it came to converting to excel, I came up with #value!
I want to enter the time in 12 hour format and using am/pm to designate. I am making it for someone to make work schedules with and they do not know military time.
View 11 Replies
View Related
Apr 2, 2009
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.
View 3 Replies
View Related
Apr 21, 2009
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.
View 11 Replies
View Related
Jan 9, 2014
I get data in the form of an .xls worksheet that includes a date column and an "hour" column. The hour column contains 1-2 digits representing the hour of the day (0-23). I need to be able to combine the date and time fields for graphing, searching, and sorting the data. No matter what I try, I can't get excel to recognize the hour as the time of day (e.g. 2 = 2:00am, 16 = 4:00PM, etc). The data sets tend to be large and I get new data in the form of new .xls files often, so a script to convert the data is probably not a good solution.
View 4 Replies
View Related
Oct 7, 2008
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
View 6 Replies
View Related
Nov 6, 2007
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.
View 11 Replies
View Related
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
Jul 16, 2012
I've calculated the time of hours worked by staff using th 24 hour clock - "hh:mm" format.
When summing up the hours, it goes a bit haywire, i.e.
Staff A = 12:30
Staff B = 14:00
Sum = 02:30
Total should equal 26:30 (26 and a half hours), however I get returned 02:30. I assume it is down to the 24 hour clock and I so I tried adding 24 to it in the formula bar to get the right answer and it didn't work.
View 5 Replies
View Related
Oct 20, 2009
If I had a cell containing a time, 06:30 in A1 for example, how would I use VBA in the following argument:
If the hour in A1
View 9 Replies
View Related
Jun 12, 2008
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!
View 9 Replies
View Related
Apr 10, 2014
Time arithmetic, I have two cells representing a time range.
The first one (say: X1) is formatted using the custom format [h]:mm and contains a certain number of hours and minutes. It gets its value by summing up other cells in the same format. A typical entry could be 98:35 to represent a duration 98 hours and 35 minutes.
The second cell (say: X2) is formatted as a number with 4 decimal places after the comma, and similarily gets its value by summing up other cells in the same format. It also represents a time duration as a number of hours. A typical entry could be 202.7500 to represent a duration of 202 hours and 45 minutes (because 0.75 of an hour is 45 minutes).
I would like to calculate the hour difference between these cells, and display it as hours and minutes. In the example given, the result should be negative, i.e. -104:10.
My first approach was to use the formula X1-X2 and format the result as [h]:mm, but this gives me a #VALUE! error.
View 2 Replies
View Related
Mar 21, 2013
I'm trying to work out the productivity of something per hour but I'm having a bit of trouble with dividing time in Excel.
Basically I've got
A1 = # of Sales (e.g. 5)
A2 = Total time worked (e.g 07:36:00)
I want C1 to show me how many Sales are made per hour.
View 1 Replies
View Related
Sep 11, 2006
I have a column with dates + time under this format: 2005-01-01 00:00:00. I wanted the same thing for the whole year, so, I wrote 2005-01-01 01:00:00. in the cell below, and then did an autofill.
Unfortunately, at some point, the cell goes from
2005-01-05 03:00:00
to
2005-01-05 03:59:59
which is a problem for me, because I was using the hour as an indicator.
And hour(2005-01-05 03:59:59) returns 3 and not 4!
View 6 Replies
View Related
Dec 6, 2006
I have a problem with entering 12-hour time data in excel.
If I key in 3:00, Excel will recognise it as 3:00 AM.
If I key in 3:00 p, Excel will recognise it as a text entry. To get Excel to recognise it as 3:00 PM, I have to use the 24-hour format. i.e. I have to type 15:00.
I have checked the Regional and Language option in the Control Panel. Everything looks fine.
Does anyone have such encounter with Excel before?
View 9 Replies
View Related
Jun 7, 2014
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.
View 9 Replies
View Related
Mar 5, 2014
I am working on a set of data that has a two week time period and specific times of events throughout each day. The dates and times are in separate columns and the time is in 24 hour format HH:MM. I am trying to count the number of occurrences in tenths of the hour or six minute increments, so 1-6 would equal .1 - 7-12 would equal .2 and so on - I really would remove the decimal and just express the value as 1-10. I am then just trying to do a count of the number of occurences for each hour and each day to see where the occurences are grouping. I will then graph this result to see where the clusters occur
View 1 Replies
View Related
Feb 27, 2009
Here is a traditional Indian time calculation
(1 day) 24 Hr = 60 N
1 N = 60 V = 24 Minutes
How to formulate this in excel 2003.
If A1= HH (hour), B1=mm (minutes) - Inputs
A2 should show NN : B2 should show VV - Result
And Viceversa.
If A1= NN (hour), B1=VV (minutes) - Inputs
A2 should show hh : B2 should show mm - Result
hh - hours, mm - minutes, NN - Nazhigai, VV - Vinadi - all are numbers
View 13 Replies
View Related
Apr 4, 2009
I need a basic formula that will take my start time from my end time and round it up to the next hour with a two hour minimum. I formated my cell to not have decimals and I tried to use round and roundup along with and if/then formula but no luck. If it makes any difference, all times are in 24hr format.
View 6 Replies
View Related
Nov 30, 2003
Is there a way to format cells so 24 hour time may be input without the colon, as an example 1425 instead or 14:25? We can do it in Access. One would think it possible in Excel, yet I have not discover how if indeed its possible.
View 9 Replies
View Related
