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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Nov 30, 2009
Is There a formula for current Month based on system clock....
View 10 Replies
View Related
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
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
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
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