Time Clock Six Minute Rounding Formula

Apr 27, 2007

I am trying to figure out the written formula for needing this answer:

For a time clock with 6 minute rounding.

If I type in 8:20 as the arrival time, I need it to round to 8:18, so that the final time will be in tenths.

Here is the rounding:

Actual Rounded Minutes in Time Clock
:57, :58, :59, :00, :01, :02
:03, :04, :05, :06, :07, :08
:09, :10, :11, :12, :13, :14
:15, :16, :17, :18, :19, :20
:21, :22, :23, :24, :25, :26
:27, :28, :29, :30, :31, :32
:33, :34, :35, :36, :37, :38
:39, :40, :41, :42, :43, :44
:45, :46, :47, :48, :49, :50
:51, :52, :53, :54, :55, :56

Anything in the same line will round to the bolded time.

Example: I arrive to work at 8:09 and go to lunch at 11:59, it will round to 8:12 and 12:00, making my time worked 3:48 or 3.80. Then I get back from lunch at 12:59 and leave at 17:14, which will round to 13:00 and 17:12, making my time worked 4:12 or 4.2 for the 2nd 1/2 of the day and 8:00 or 8.0 hours for the day.

Time In----------Time Out----------Total Time----------Decimal Time----------Total Hours
...8:12.................12:00..................3:48......................3.80
..13:00................17:12..................4:12......................4.20..........................8.00

The problem is I want to write the actual time and not have to round everything myself and yet I need the decimal time to match what my time clock actually calculates from.

My current formula for the decimal shown above as 3.80 is this: =(HOUR(F7)*60+MINUTE(F7))/60
My current formula for the total time for the day (regular hours) is this: =IF((((E7-D7)+(I7-H7))*24)>8,8,((E7-D7)+(I7-H7))*24)
My current formula for overtime is this: =IF(((E7-D7)+(I7-H7))*24>8,((E7-D7)+(I7-H7))*24-8,0)

This is not a time clock, it is my personal copy of what my time for the week was/is, so that I can compare it to the actual time card that I have to sign off on.

View 9 Replies


ADVERTISEMENT

Macro That Runs Every Minute When System Clock Changes Minute?

Sep 13, 2013

create a Macro that runs when system clock changes minute and then at every change of minute of system clock.

For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...

I already have a Macro that runs every minute from initial run time, using

Code:
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"

but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.

View 9 Replies View Related

Rounding Time Value To Nearest 6 Minute Increment

Mar 16, 2007

What formula could I use to round up or down time values to the nearest 6 minute increment?

Example:

Time ----> Rounded To
5:02 PM --> 5:00 PM
5:03 PM --> 5:06 PM
4:18 PM --> 4:18 PM
4:19 PM --> 4:18 PM
4:20 PM --> 4:18 PM
4:21 PM --> 4:24 PM
etc.

View 6 Replies View Related

Active Clock That Updates By The Minute

Feb 4, 2007

Is there anyway of getting an "Active Clock" in a cell.
I know the =now() thing, but this is not what I am looking for,
I would like to get a clock that updates by the minute, without having to select a cell, or make excel do a calculation.

View 9 Replies View Related

60 Minute Time To 100 Minute Time

Jan 8, 2008

Is there a formula that will calculate standard 60 minute time in to 100 dinute time? Example 2:21 = 2:35. I know how to get to the answer with a calculator, but would like a formula to cut out all of the extra steps.

View 3 Replies View Related

Select A Time From A Clock?

Jun 12, 2013

I've linked some cells to the date picker function so a date can be selected from a pop-up calendar.

Is it possible to do the same with a clock function?

View 2 Replies View Related

Use Excel As Time Clock?

Sep 6, 2005

I currently use a manual time clock for my employees to punch in and out. I then use Excel to tally their times. This is very tedious and error prone.

Is it possible to use Excel as the timeclock itself? I know that the employee can enter the time in a spreadsheet manually. But this also opens the door for error and potential dishonesty.

What I am looking for is, if the employee can punch in and out by doing a keystroke and Excel uses the computer's clock to timestamp.

View 9 Replies View Related

Losing A Minute When Subtracting Time?

Jan 6, 2014

In cell L7 I have (80/60)/24 and formatted h:mm to get 1:20. In cell M11 I have 1:00 (h:mm). In cell M12 I have L7-M11 and get the result 0:19. Why I'm losing a minute and not getting the result 0:20?

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

Round Time Value Like 08:23:51 To Nearest 15 Minute Like 08:30:00?

Jun 21, 2012

How could round a time value like 08:23:51 to the nearest 15 minute like 08:30:00?

View 2 Replies View Related

Time And Cost Calculations Per Minute

Oct 17, 2007

I have costs per minute and call times in seconds

How do I work out the cost per call in Excel?

It's probably something very simple, but I just can't work it out. No matter what I try, I can't get it right

View 9 Replies View Related

Time (clock) Times A Number

Apr 2, 2009

I have a start time in one column, end time in another and a column with the difference between the two. In another column I have profit. In the last column I have profit per hour where I divide the profit by the hours, but since one is hours and the other is a number it gives me an error.

View 4 Replies View Related

Time Related To 24hr Clock

Jun 22, 2009

What i try to achieve is: from 00:00hrs to 24:00hrs = 24hrs which is 1.0 day
Have made the attached spredsheet to calculate it correctly to 1 day. But if you look on the attachment the "total hrs" is saying 00:00 (it transfers to 00:00 when i am putting in 24:00). Basically how am I able to make Excel to display 24:00 insted of 00:00. Maybe there is an add ins availible like the pop up calendar but for times instead.

View 4 Replies View Related

Time Clock And Converting To 3 Min Increment

Jul 8, 2009

Okay I have a basic Time clock calculation in excel (i.e.-(timeout1-timein1)+(timeout2-timein2)="Total time"). What I need to do is convert the "Total time" to be changed to 3 min increments. I don't know if that is the best way to put it but let me show you the chart for the conversions.
3 mins=.05 hrs, 6 mins=.10, etc etc etc.

So lets put this into us

A1 (timein1)= 6:42 AM
A2(timeout1)=11:30 AM
A3(timein2)= 12:00 PM
A4(timeout2)= 5:00 PM (17:00)

So A5(Total Time)=9h 48min, but I need it to equal 9.80 hrs because with the conversion, 48 mins is .80 of an hour. So how can I set up A5 (Total time) to automatically do this calculation? This website is how I would like it to work http://www.1728.com/timecard.htm

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

Round A Time Entry To The Next 30 Minute Increment

Feb 2, 2006

I have a worksheet that I enter time values into individual cells:

A1 = 6:00
B1 = 12:00
C1 = 12:45
D1 = 17:15

When I enter these values, I would like some of the values to round to the
next higher "30 minute increment" such as Cell C1 should become 13:00.

I would also like some of the values to round back to the next lower "30
minute increment" such as Cell D1 should become 17:00.

View 12 Replies View Related

Time Countdown - A Live Clock In The Spreadsheet?

Nov 5, 2011

I need to calculate how much time I've got left before the earliest order needs to be despatched (it might be 2 or more on every day). This wouldn't be a problem if orders would be placed daily. But for every item it varies. For example: for "X" product there is an order in 3 days time to be despatched at 19:00. I have built a live clock in the spreadsheet but I can't work out the formula.

P.S. Also I need two time formats, first - days(text) hh:mm second - just a numeric value that can be formated as [hh:mm].

View 5 Replies View Related

Running Clock In Excel With Different Time Zone

Nov 28, 2013

Need running clock in Excel... in A1 I have put running clock which is taking current system time. However, I want to add running clocks for EST in B1 - CST in C1 and PST in D1.

VB code I used to display current system running clock

Global clockOn As Boolean
Sub runClock()
Range("A1").Value = Now()
If clockOn = True Then

[Code] ......

Attached File : Time.xlsm‎

View 3 Replies View Related

How To Make Clock To Show Time In Words

Aug 29, 2013

I want to make a Word Clock. Instead of showing the time as "12:30" I want it to show the words "It is half past twelve"

To Do this I've created a Square of Letters on an excel sheet (one letter per cell!) with black letters and black back grounds, when it is 12.30 I want the relevant letters to (cells) to change the text coulor to white so it shows up.

The cell I'm using are from D8 to P19 and the time is shown in A1

So lets say at 12.30 Cells D9, F12, H14 and M16 need to change from Black Text Colour to White Coulour,

I need a code or some VB that says "If A1 = 12.30 the Cells D9,F12,H14,M16 = White Text Colour, if not Black Text Colour".

View 1 Replies View Related

Manual Time Clock For My Employees To Punch In And Out

Sep 6, 2005

I currently use a manual time clock for my employees to punch in and out. I then use Excel to tally their times. This is very tedious and error prone.

Is it possible to use Excel as the timeclock itself? I know that the employee can enter the time in a spreadsheet manually. But this also opens the door for error and potential dishonesty.

What I am looking for is, if the employee can punch in and out by doing a keystroke and Excel uses the computer's clock to timestamp.

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

Formula For Minute Subtraction?

Mar 6, 2014

What is the formula for taking two times, and finding the minutes between them. E.g. 12:35 PM-12:28 PM=7. I know i can use (A1-A2)*1440, but is there an easier way?

View 4 Replies View Related

Time Now & Rounding Up

Mar 16, 2009

I have a spreadsheet which has a daily schedule. It goes from 8am to 9pm in 15 minute slots.

I am looking to do a Time Bar. Using Conditional Formatting I was wanting to know if the time now can be rounded up or down in any way

Example, As I write this the time is 15:37 I have this in a cell F4

In row8 Cells C to BC I have the time in 15min slots like 08:00 08:15 08:30 and so on. What I was thinking was could I round the time from 15:37 down to 15:30 so I can use a Conditional Format to trak the time in my schedule?

Or is there an alternate way?

View 6 Replies View Related

Rounding Down Time

Jan 20, 2009

How can i rounddown time in VBA

I got a variable value EX: 00:30:08/// 0h:30m:08 second.

How can i round it down to the nearest EX: 00:30m:00

Narurally, the variable as not always the same value.

View 9 Replies View Related

Time Rounding Macro

Oct 6, 2008

I am in need of a Macro that will take the time entered into cell E12 and have a macro automattically run and round that time to the nearest 1/4 hour and show it in cell E12 and replace the entered value..

View 9 Replies View Related

Formula: Current Month Based On System Clock

Nov 30, 2009

Is There a formula for current Month based on system clock....

View 10 Replies View Related

Time (clock) Times A Number= Very High Number?

Apr 2, 2009

I have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent)

On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.

View 2 Replies View Related

Excel 2010 :: Rounding Negative (and Positive) Time Values?

Apr 24, 2013

EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.

View 9 Replies View Related

Interpolating Data At 5-minute Resolution Down To 1-minute Resolution

Nov 27, 2013

Entering a formula which will allow me to expand my data set by interpolating between sets of x,y values. My data set is in 5-minute resolution and I would like to interpolate so that I can output a new set of x,y values in 1-minute resolution. Here is my example of what I have and what I would like my end result to be, and also I have attached a sample of my data set in the below excel file:

Original data set
Date TimeTemperature oC
6/12/13 13:30 18.28
6/12/13 13:35 17.9
6/12/13 13:40 17.9

Desired data set
Date TimeTemperature oC
6/12/13 13:30 y
6/12/13 13:31 y
6/12/13 13:32 y
6/12/13 13:33 y
6/12/13 13:34 y
6/12/13 13:35 y
6/12/13 13:36 y
6/12/13 13:37 y
6/12/13 13:38 y
6/12/13 13:39 y
6/12/13 13:40y

datetime_temp.xlsx

View 2 Replies View Related

How To Remove Rounding Formula

Jan 16, 2013

Is there a quick way to remove the rounding formula from my spreadsheet of over 1000 cells with the formula? I would literally have to go to each cell and delete it since the formulas are not consistent in each cell so I can't just copy and paste the new formula from on cell without the rounding funcntion.

Is there a quick way to remove it?

View 5 Replies View Related







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