Time/Number Anomaly
Jul 9, 2008
Cell E11 - 3:00 PM
Cell E12 - 11:30 PM
Here's the formula I am using to determine the amount of hours from the in time E11 to the out time E12. I'm using three helper cells to get the amount of hours...
Cell N11 =IF(MINUTE(E12)>MINUTE(E11),MINUTE(E12)-MINUTE(E11),MINUTE(E12)-MINUTE(E11))
Cell N12 =IF(HOUR(E12)
Cell N13 =((ABS(N12*60)+N11)/60)
N13 produces 8.5 hours, which is correct, and everything works as long as the out time stays less than 12 AM. However, if I enter 1:00 AM, everything turns to zero?
How can I write a formula in ONE CELL only that converts the amount of hours worked from in time (E11) to out time (E12), that can assume 3:30 PM to 1:30 AM is 10 hours? It has to assume that E12 is always greater than E11.
View 9 Replies
ADVERTISEMENT
Sep 19, 2008
I have a worksheet that has data that changes each month that I need to compare to the previous month.
I would like to run a macro to check both worksheets (which I will copy into a new workbook and run the macro against the 2 worksheets from another workbook) and then the results would be put into a 3rd worksheet in that workbook.
The new month/worksheet can have additions and deletions from the previous month/worksheet, I would also like to distinguish that as an "add" or "removed"
View 14 Replies
View Related
Dec 8, 2013
I have an anomaly with this loop that seems to be a bug, but I need a second opinion on it. The split statement is working and produces a single array. The Set col statement works on the first iteration and post the value onto sheet 2. The problem is that after the first iteration the Set col statement will not set, although the dys(i) value is correct, The Find function apparently does not return the range value.
Code:
dys = Split(c.Offset(0, 1), ",")
For i = LBound(dys) To UBound(dys)
Set col = sh2.Rows(1).Find(dys(i), , xlValues, xlPart, MatchCase:=False)
If Not col Is Nothing Then
If i = LBound(dys) Then
[code]....
View 4 Replies
View Related
Jul 1, 2008
I have a Lookup that updates name from a list.
D2 =IF(LOOKUP($A$2,B:B,C:C)=",",LOOKUP($A$2,B:B,C:C)) - this Works fine
Say A2=3
C1= Name1
C2= Name2
C3= Name3
Also a macros that if D2 is typed over you can re-insert the LookUp.
Sub Reset()
For Each ce In Range("D12) 'Company
'If IsEmpty(ce) Then
ce.FormulaR1C1 = "=IF(LOOKUP(R2C1,C[-2],C[-1])=",",LOOKUP(R2C1,C[-2],C[-1]))"
ce.Font.ColorIndex = 11
'End If
Next
End Sub
The Weird thing is that in stead of putting the value from the LookUp it paste the formula
=IF(LOOKUP(R2C1,C[-2],C[-1])=",",LOOKUP(R2C1,C[-2],C[-1])) like it was text
I have used the reset on a number of numeric equations no problem, but not on a LookUp one. I've tried changing the format of the cell, but not successfully.
View 9 Replies
View Related
Mar 3, 2009
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies
View Related
May 5, 2006
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50
Start time: 12:30
Finish time: 23:00
Hours worked: 10.50
View 9 Replies
View Related
Sep 8, 2009
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
View 2 Replies
View Related
Nov 18, 2008
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View 9 Replies
View Related
Dec 24, 2008
I have a userform that has a time field on it and then the userform displays the time as a number.
Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.
View 10 Replies
View Related
Oct 8, 2007
I need to separate a list of times from a telecommunications bill. I have 170,000 line of data.
The Times are represented as below.
eg
46 46 Sec
59 59 Sec
100 1 Min
159 1 min 59 sec
200 2 mins
502 5 mins 2 sec
1256 12 mins 56 sec
3456 34 min 56 sec.
How do I separate them to a decimalized per min value.
View 5 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
Feb 16, 2014
I have a time sheet in which i need user to enter a time sheet number in cell c14
I need a formula for that cell so the user can input the time sheet number but only be able to do this if the time sheet number they are imputing is the next number in sequence of the last one used.
View 1 Replies
View Related
Dec 4, 2012
I have a cell that has a formula in it (F11) the formula produces a number with no decimal places and that number represents a time in minutes. I have another field (K11) which has a time in it. In another field I want to add the minutes of cell F11 to the time in K11.
F11 = 29
K11 = 7:00 AM
When I just add the two cells : =J12+F11 the returned result is 8:42 PM.
Why do I get 8:42 PM?
I need the answer to be 7:29 AM. How do I get that?
View 1 Replies
View Related
Apr 16, 2014
My question is i try to change the numbers in to min and sec like this here below.
Table row:
300 > in to this 3:00 and not like this > 00:03:00
1000 > in to this 10:00
10000 > in to this 1:00:00
that all automatic for the whole row.
Is that possible ?
View 5 Replies
View Related
Dec 9, 2012
I have a time as 0:20 in one cell. I want to covert that to the number 20 in another cell. Is this possible to to and can I get an example of how?
View 2 Replies
View Related
Aug 30, 2013
I exported a report from Crystal to Excel. The times came over as numbers. How do I convert the numbers into time.
Example: 753 to 7:53
1426 to 14:26
View 4 Replies
View Related
Oct 27, 2008
I have tried to make a VBA that would allow me to type in:
10657 and have the VBA to automatically convert that into a timeformat -> 1:06:57.
It works ok as long as the typed number is 4 digits or shorter.
If i type 12345 I would expct to get 1:23:45 but I get 00:00:45
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VaValue As Variant
TCol = Target.Column
If Not (TCol = 5 Or TCol = 6) Then Exit Sub '
View 9 Replies
View Related
Dec 10, 2008
I am trying to compile data from several different managers within my company. They all have similar workbooks, and have been entering time in a number format. Currently they have values that look like this:
Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD11.351.18 22.012.23 32.333.05 44.124.22 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
However, the data should appear as this:
Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD1=DEFG101:3501:18 202:0102:23 302:3303:05 404:1204:22 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
So, in any instance the period or decimal point should be a colon and the format of the cells should be in [m]:ss.
I want to be able to select a range within the workbook and run a macro that will convert the numbers into the "[m]:ss format.
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
Sep 5, 2009
field A1 & B1 are variable user input. The goal is to subtract B1 from A1, take the result and divide it by 176 (a fixed number) and display the result as minutes:sec.
For example:
A1 B1
14,000 (minus) 2,200 (= 11,800 /176 =) 67
I want to result (67) to display in field C1 as 01:07 (min:sec)
I tried (A1-B1)/176 and formatted C1 to be min:sec but the result displays as 5:27 I have no idea what that number represents.
View 4 Replies
View Related
Mar 5, 2012
Is there a way i can convert the time to a specific number? for Eg. If incell A1 i have the time in following format 01:15:08 (MM:SS:MS)
i want the result in A2 to show 7508
01 minute = 60 seconds
Plus 15sec = 60 + 15 = 75
and 08 miliseconds would give 7508 ??
View 1 Replies
View Related
Feb 20, 2014
I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?
View 8 Replies
View Related
Nov 12, 2008
20081110120000[0:GMT]
is there a way to convert many entries of dates and time entered in the above way into actual dates and times? is it as simple as making a custom format? if so, whats the syntax?
the entries appear to be the year(4 characters), the month (2 characters), the day of month (2 characters), and the time of day (6 characters, AM/PM)
i dont need the time, so the last part ("120000[0:GMT]") isn't necessary.
View 9 Replies
View Related
Feb 20, 2009
I have created a simple spreadsheet to keep track of work hours. I simply enter in each days hours and then I get a total. I have each cell formatted for time (hh:mm). However it is annoying to have to type in the colon for each days time.
Is there a way I can just type in the three digits '9,3,0' into the cell and have it come out as '9:30'?
View 9 Replies
View Related
Mar 13, 2013
Looking for a formula to convert time (00:00:00 AM) to a general 4 digit number (0000). Example 08:30:00 AM ---> 0830. Tried using format cells but no luck.
I was able to convert a standard number to time using the following formula: =TIME(INT(H2/100),MOD(H2,100),0)
View 2 Replies
View Related
Jun 15, 2014
I have a data with phone number, date and time.I want to track how many number of calls came on a particular date and during a particular time. formula which would work in excel and track the same.
View 2 Replies
View Related
Dec 9, 2013
how to convert number to data time format:
eg. I have number 20121231230000 and I need its conversion to 31/12/2012 23:00.
I have tried to use fucntion date with left, mid, right but still I do not know how to extract time.
View 2 Replies
View Related
Jan 15, 2014
I have two Columns of 4 digit numbers (24 hour clock format). I need these numbers to be in Time Format. Formatting the Columns or even individual cells seems to do nothing.
1238 as 12:38 etc.
These numbers are acquired using VLOOKUP
I have attached a copy of my file, The numbers in question are on sheet "iCalender" 8th sheet accross.
Columns "start Time" and "End Time"
Time Format is Compulsory so that I can upload to a .ics Web Converter. Of which is currently failing for this reason.
5th January 2014 - Copy.xlsm
View 13 Replies
View Related
Apr 28, 2014
I am looking for a way to calculate the average time, over a number of times.
So basically, would have different times in 24 hour hh:mm:ss, all in column i.
I have tried =AVERAGE(i2:i18) and it doesn't work.
I have also tries Ctrl/Shift enter as an array, it didn't work.
I tries to format cells as time and number, it didn't work!
View 1 Replies
View Related
Apr 6, 2009
A1 has 3:11:00 PM as a cell value (displayed as - 3:11). I want A2 to = 3 and A3 to = 11. no concern about the seconds or the AM/PM
View 5 Replies
View Related
