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


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

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

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

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

Using COUNTIF To Determine # Of Employees Working Each Hour

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

Convert Hh:mm.* To 12 Hour Time Format

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

Counting The Number Of Extra People Working Each Hour?

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

Convert Day:Hour:Minute To Valid Times

Sep 12, 2007

We have a report application and developers that are giving us a real challenge.

1) if you have a cell formatted with:

DD:HH:MM and have a 4 digit numeric value for each section:
47.00:0.00:59.00
you can use the following formula to convert to hours minutes that excel can handle:
=(LEFT(P18,4))+MID(P18, FIND(":",P18)+1,4)/24+MID(P18, FIND(":",P18,7)+1,4)/1440

2) if you have a cell formatted with:
DD:HH:MM and have a 2 digit numeric value for each section:
47:00:59
you can use the following formula to convert to hours minutes that excel can handle:
=(LEFT(P4,2))+MID(P4, FIND(":",P4)+1,2)/24+MID(P4, FIND(":",P4,5)+1,2)/1440

Problem:
our developers have created a new report that now has a variable format
D:H:M
2:6:33
4:3:3
0:22:5
0:2:2
22:0:1..................

View 8 Replies View Related

Convert Hour / Minute To Another Predefined Time Mode

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

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

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

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

Formula That Allows Me To Type In Their Hour Work If It's Greater Than 40 Hours

Dec 28, 2006

I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.
We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)

My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?

Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00

If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2

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

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.

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

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

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







Copyrights 2005-15 www.BigResource.com, All rights reserved