If Statement For Hour And Min

Sep 1, 2009

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

ADVERTISEMENT

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.

View 9 Replies View Related

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

View 3 Replies View Related

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)

View 3 Replies View Related

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.

View 1 Replies View Related

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

View 4 Replies View Related

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.

View 9 Replies View Related

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!

View 9 Replies View Related

Utilize A Select Case Statement In Target Intersect Statement

Jul 28, 2009

I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.

My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.

I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?

View 5 Replies View Related

Hour (now)

Sep 27, 2008

I have this

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

View 9 Replies View Related

Run VBA Every 24 Hour Only On Weekdays

Aug 28, 2012

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.

View 9 Replies View Related

Event By Hour And Age?

Apr 28, 2014

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

View 8 Replies View Related

How To Get A Macro To Run Exactly On The Hour And Day

Dec 4, 2006

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

Change To Hour

Jan 10, 2008

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

View 9 Replies View Related

24 Hour Timesheet

Nov 16, 2009

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

View 9 Replies View Related

Run Macro Every Hour

Aug 7, 2008

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

View 5 Replies View Related

Counting Staff By Hour

Jul 28, 2014

I need counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.

Basically:

00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42

01:00
02:00
"
"

Even when I think I've gotten close the midnight time crossover keeps throwing off.

Schd_05_TEST.xlsx

View 3 Replies View Related

24 Hour Time Adding

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

Converting Digit To Hour

Nov 3, 2006

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 ?

View 11 Replies View Related

24 Hour Double Time

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

24 Hour Countdown Function

Sep 19, 2013

I have a countdown in my work sheet that counts 30 days from today if Cell A11 has a value and the date is in B11. Below is the function.

=IF(A11"",B11+30-TODAY(),"")

I love the function above because it counts in reference to a value. I would like something like this function but I want it to countdown 24 hours and not 30 days from today.

View 5 Replies View Related

Hour (0-23) To Time In Excel

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

Counting Entries Per Hour

Nov 16, 2007

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?

View 9 Replies View Related

Chart Over A 24 Hour Period

Mar 13, 2008

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?

View 9 Replies View Related

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

View 9 Replies View Related

Calculating Episodes Per Hour

Jul 30, 2009

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?

View 9 Replies View Related

Automatically Show Every Hour

Dec 5, 2006

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

View 5 Replies View Related

Count Time (entries Per Hour)

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

Job Hour Tracking With Pivot Tables

May 19, 2009

I have an Excel timesheet that the supervisor loads the time (in and out) for all the shop employees. These employees may work up to six different jobs in one day at various points throughout the day. I have no problem with calcs and formats on the daily time entry tab (sheet1). What I need is a way to track each job using a running total so that as the job is in progress we can see how we are doing against estimated hours. I thought perhaps I should at least make a weekly time summary pivot table thinking that it would make it easier to calculate, but it didn't help with running totals. The summary included (5) copies of the same daily entry tab (sheet1) just given a different name, in this case ..a simple date change and save the sheet with the date used. OK, here's the problem. How do I create a running total that would track each job? I forgot to mention earlier that each daily time entry sheet is saved and named by the date for which it represents. The weekly time summary pivot table works fine....but it only gives me the grand total of hours worked for each day...not a running total summary. I can view the pivot tables and go to the calcualtor and add the hours up per job, but that's defeating the purpose. Anyway, I hope this is clear enough to at least get some responses. Then, maybe by then I'll have a better understanding how to ask the right questions.

View 9 Replies View Related

Converting Numbers To 24 Hour Format?

Dec 12, 2013

how we could convert numbers to 24 hour time format as we key in the numbers?

Example:

As we key in 0815, it would automatically convert to 08:00.

As we key in 1345, it would automatically convert to 13:45.

What is the code we could use to put in the code of the sheet?

View 6 Replies View Related

Subtract 4 Hour From All Cells In A Column

Mar 15, 2014

I have a column of times that are formatted 12:30:00, 14:02:00.....so on and so on. I want to convert these times to be 4 hours less than what they are. Is there a formula I can put in a cell somewhere that will do this for me?

View 9 Replies View Related

How Do I Set Up A Formula For Parts (or Units) Per Hour

Apr 12, 2006

I'm trying to set up a spreadsheet that tracks total hours worked and total
units produced. Then I need to have a column that shows how many units per
hour were produced.

Currently, I have something like this:
Column A is in elapsed time [h]:mm
Column B is a Number with two decimal places
Column C divides Column B by Column A

However, I get strange results. For example:
Column A is 6:24:00
Column B is 13
Column C shows 120.00

13 parts in 6:24 hours should be something like 2.1666 parts per hour!

View 10 Replies View Related

Calculating Time Across A 24 Hour Period

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

Rounding Timesheet To Quarter Hour

Oct 19, 2008

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.

View 6 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

Procedure / Macro To Run Automatically Every Hour?

Apr 11, 2003

VBA - I want to run a set of statements every hour on the hour for the lifetime of the workbook being open

View 4 Replies View Related

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

View 4 Replies View Related

Calculating Time (24 Hour Clock)

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

Timesheet - Round DOWN To Nearest 1 / 10 Hour

Jan 2, 2013

I am trying to figure out a formula that will provide the following results for time worked. (This is contract work that is billed in 1/10 hr increments.)

1 min - 5 min = no charge (rounddown to 0 min)
6 min - 11 min - rounddown to 6 min
12 min - 17 min - rounddown to 12 min.

or

1 - 5 min are billed at 0 hours
6 - 11 min are billed at .1 hour
12 - 17 min are billed at .2 hours

B15 = start time, C15 = end time, G15 = billable time in 1/10 hrs

View 4 Replies View Related

Filter Column Of Dates By Hour Of The Day

Jul 24, 2013

I have a database that includes a column containing dates which include a decimal time value e.g 2013/07/31 16:14

VBA that will allow me to filter to the records where the time element of the date is between two values e.g. between 12:00 midday and 17:59.

There will be many thousands of records covering a wide time period, so the code needs to ignore the date part and just concentrate on the time.

Probably dead easy but it's 17:04 and I have brainfreeze.

View 3 Replies View Related

Making Formula Calculate At The Top Of Hour

Apr 19, 2014

I have a spreadsheet that I use to track my stocks. I have installed a third party add-in that goes out to the internet and grabs various stock information. But, when you are pulling info on about 30 stocks and getting info like name, price, dividend, div pay date and such, it slows the spread sheet down tremendously. Is there a way to keep the value in the cell and change it only during a certain time period such as 5 minutes before the top of the hour to five minutes after the top of the hour.

View 2 Replies View Related

ADVERTISEMENT