If Function Not Returning Value When In Time Format
Dec 9, 2012
I am working on a spreadsheet that deals with times such as a work schedule, how every the schedule cells are in a time format, and my if function will only return "check"(which is false). my code works if it remove the time formatted cells but i want to keep the document in its orginal format.
=IF(Mngmt!F16="PH","",IF(Mngmt!F16="OFF ","",IF(Mngmt!F16="VAC ","",IF(Mngmt!F16="8:00:00 AM", "MIKE", "CHECK"))))
The bold and under line portion is where my problem is at.
Its looking to the mngmt tab to find f16 when it equal 8:00:00 AM (scheduled time in time format) but returns ""check"(false).
View 4 Replies
ADVERTISEMENT
May 10, 2007
I have two worksheets which talk together and one of them gives a time stamp using some VBA code
=format(Now(),"dd/mm/yyyy AM/PM"
in the other spreadsheet I have the Now() function which has been working fine. However, when another user went into the spreadsheet went in it, my calculations between the two dates now error. I am presuming its because of the now function not having some formatting defined so is there anyway of changing this value? The cells-formatting will not obviously work as excel is getting confused between the two time types even though the format looks fine. To help the calculation that is messing up is
=IF($L7="","N",IF(DATEDIF($L7,$J$1,"d")>10,"N","Y"))
whereby L7 is the imported timestamp and J1 is now(). The calculation is to show which accounts become inactive after 10 days.
After reading up on the internet I know that to get round the problem I have to make sure that everyone is using the same regional settings. But I just have a couple of questions:-
1. I can understand the date calculation not working on the other persons machine, but when I went back to look at it I was getting the same error. Even though the file is shared, how come my settings didnt 'overwrite' and how come i was affected?
2. Is there a way to make the code work regardless of who opens it in whatever region? Im not sure if there is a fundamental problem but I cant rely on everyone having the same settings because I know that this will cause problems in the longterm.
3. Using the formuale Now(), is there no way of forcing that to a uk date format to stop the errors?
View 9 Replies
View Related
Jul 28, 2006
I have a querytable running on my sql database. In the query I have a trimleft function because the numbers are stored with several proceeding blank spaces. When I execute the query through my macro, everything works fine, except I can't average or sum the results. They can be multiplied, divided, etc, but not averaged or summed (they just show as zero on summing, which makes the average DIV/0). The really strange part is that if I select a number and go to the formula bar and hit delete at the beginning of the number (which actually deletes nothing), the number is suddenly capable of actually performing as a number. I assume this has something to do with how the querytable is returning my results, but does anyone have an idea for a workaround for this issue?
View 3 Replies
View Related
Sep 17, 2008
I have an IF Function that if C4 says HUD then it returns the infor in a range of cells on the HUD worksheet, which is a separate worksheet from where this formula is {=IF(C4="HUD",HUD!A1:C100,"")}. I have also used =IF($C$4="HUD",HUD!A1,"").
But now, on the cells that it is returning, it puts 0 in cells that are blank on the other worksheet. What can I add to this formula to eliminate that?
Both of these functions are working otherwise. They are just returning 0 in cells that were blank.
View 9 Replies
View Related
Oct 15, 2009
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09
View 2 Replies
View Related
Nov 7, 2009
I'm the Held desk manager . I'll have my folks logging in every time interval and work for 9hrs. Now I wanted to see at any given point a day the number of folks I have on my desk .
I have created table " A " and I'm looking for a formulae so that I get my data like table " B " .
To give more insight about table '" B " - During 00:00 - 00:30 , I'll have 41 people slowly goes down as people log off..
View 13 Replies
View Related
Aug 9, 2009
the Times work fine 22:00pm to 5:30am or 6:00am to 5:30am
Formulas
J4
=IF(ISNUMBER(D4),(D4>E4)*MEDIAN(0,E4-N$4,O$4-N$4)+MAX(0,MIN(O$4,E4+(D4>E4))-MAX(N$4,D4))-F4-L4,0)
K4
=IF(ISNUMBER(D4),MAX(0,MOD(E4-D4,1)-J4-L4-M4-I4-F4),0)
L4
=IF(ISNUMBER(D4),IF(MOD(E4-D4,1)-F4>P$4,(MOD(D4+P$4,1)>E4)*MEDIAN(0,E4-N$4,O$4-N$4)+MAX(0,MIN(O$4,E4+(MOD(D4+P$4,1)>E4))-MAX(N$4,MOD(D4+P$4,1))),0),0)
M4
=IF(ISNUMBER(D4),MAX(MOD(E4-D4,1)-F4-I4-L4-P$4,0),0)
but if I try 22:00pm to 6:00am or later
The following is displayed
Negative Time in J4 and the wrong result in K4
this is not what I need
It should be showing the following
J4 2:00 K4 5:00 L4 00:00 M4 00:00
View 9 Replies
View Related
Nov 7, 2008
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies
View Related
Apr 24, 2014
trying to create a 'countdown' to time of macro completion (based on a static performance time of 1:48). What I want to do is compare a time (ProgTime) set as static at the beginning of the code with 'TIME' and return the difference in seconds. I use the following code:
[Code] ....
When I step through, I can see that ProgTime and Time have different values and yet 'Test' always returns a value of '0'. I tried using 'Now' instead of 'Time' and the same result ('0') occurs. I have experimented with declaring 'Test' as an integer, a long and a date and the result is always the same! I've tried using 'Seconds() around the datediff to ensure an answer in seconds, I've tried 'format' around it with multiple decimal places to make sure I'm not missing a tiny value: Nothing seems to do the trick!
View 13 Replies
View Related
Aug 18, 2009
I am new to VBA programming in Excel (currently 2003) so hopefully this isn't a "dumb" question.
I would like to re-arrange a group of data from a table of several rows and columns to a single column where the data from the first row is translated to a column then the second row is appended to the end of that data ....
ie:
( 1 ) ( 4 ) ( 7 )
( 2 ) ( 5 ) ( 8 )
( 3 ) ( 6 ) ( 9 )
to
( 1 )
( 4 )
( 7 )
( 2 )
( 5 )
( 8 )
( 3 )....
I thought a user defined formula would be a good place to start but am having problems getting the address of the cell that the function resides in to use to calculate the offset from a fixed cell (to be entered into the function as a argument). In other words, how do I get the address of the call that a UDF is entered in into the UDF? activecell.address is returning the cell that was last clicked not the cell were the UDF resides.
Secondly is there a better way to achive the results that I am looking for other than through a function? The size of the table will vary from worksheet to worksheet. I would also like to add an argumnet that lets me exclude cells with certain content from the resultant list.
View 6 Replies
View Related
Feb 3, 2007
I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.
View 2 Replies
View Related
May 18, 2003
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.
Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?
I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
View 9 Replies
View Related
Sep 6, 2006
I have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971
View 6 Replies
View Related
Apr 30, 2008
The purpose of my code is to time production cycles for heavy equipment. There are five - six segments in the cycle, and it's possible that not every segment is included in each cycle. It's also possible that the cycle will not go in sequential order.
Here's a snippet of
Sub MacroD()
Dim LR As Long
LR = Range("D" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Unprotect
Range("D18:D" & LR).Value = Now
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub
Sub MacroF()
Dim LR As Long
LR = Range("F" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Unprotect
Range("F18:F" & LR).Value = Now
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub
My problem is that when the macro runs, it returns the time in more than one row and overwrites the previous timestamp. It starts in row 17, and will record the first time in 17 and 18. When the macro is run again, the time will be recorded in 18 and 19. When it's run a third time, the timestamp is recorded in 18, 19, and 20. So essentially, I'm losing all of my previously recorded data. When I finish, all I have left is the timestamp in row 17 and whatever the last time recorded is.
View 13 Replies
View Related
Sep 2, 2009
I have data concerning documents people have to write, which is updated weekly. Is there a way of looking up a name and then returning all the documents that they have to write. I have tried a vlookup, but this only returns the first entry and not the lot. Also i would prefer the document data for each person to be returned in to one cell.
View 3 Replies
View Related
Dec 11, 2009
I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.
View 4 Replies
View Related
Jun 15, 2009
I have a function that works out which is the last column containing data on a worksheet, but for some reason it is alwys retuning 0.
View 14 Replies
View Related
Apr 27, 2007
My problem is that I am getting the N/A error in cell M3 if L3 is empty. I would like the forumla to 'ignore' cell L3 if it is empty. I do not want a zero put in there. I have looked up information on ISBLANK function but the more I read the more confused I am getting
View 6 Replies
View Related
Oct 12, 2007
I have two columns A & B. If I enter "Short" into Col A, I want "Long" to automatically appear in Col B, and vice versa. This is the code I have created so far. =IF(C4="Short","Long",IF(C4="Long","Short",IF(C4="",""))). This works but when I paste the formula into all cells in Col B, I get "FALSE" showing up in every cell. How can I stop the "FALSE" from populating every cell in Col B while Col A remains blank?
View 4 Replies
View Related
Oct 12, 2009
I have a cell with both date & time "10/9/09 3:15" This is put in the current cell by formula which indexes two dif. cells, Now I am trying to copy this cell and paste into another book but like to have only date. How can I do that? Each time I try it gives me the time value in the pasted cell and I cannot even format it.
View 5 Replies
View Related
Sep 23, 2009
I have a file that has the Date and Time combined into one cell. I want to separate the two, and cannot find anywhere on the net to do so!
This is the cells format:
d/mm/yyyy h:mm
Cells look like this:
28/05/2008 12:30
View 6 Replies
View Related
May 18, 2003
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.
Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?
I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
View 9 Replies
View Related
Dec 28, 2006
I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?
View 6 Replies
View Related
Nov 28, 2013
How do I convert 11.20.00 in A1 to 11:20:00 in B1?
I've tried =TEXT(A1,"hh:mm:ss") to no avail.
View 3 Replies
View Related
Jun 14, 2014
I have been facing few problems while automation my production sheet through formula.
1-- I want to sort data in the descending order through below formula.
=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))
when two candidates have the same percentage then this formula does not work.
View 1 Replies
View Related
Jan 24, 2014
I'm trying to compare three formula results and return the largest number. Ex.:
2217922
The first three numbers are formula results and the last is the following formula:
=IF(O5>P5,O5,IF(P5>Q5,P5,Q5))
Unfortunately, with the list below, you can see the last number (the comparison formula) isn't always the greater of the three:
2217922
2017920
01799
01399
01399
01299
01299
01299
01299
01199
01199
01099
0088
0077
0077
I've also tried other formulas, with no success:
=LARGE(O5:Q5,1)
=MAX(O5:Q5)
=IF(MAX(O5:Q5)=O5,O5,IF(MAX(O5:Q5)=P5,P5,IF(MAX(O5:Q5)=Q5,Q5,"Error")))
What's odd is that if I create the formulas above independent of the specific formula data (O5, P5, and Q5) and just type in numbers, those formulas do exactly what they should. Can I not compare data returned though formula calculations?
View 12 Replies
View Related
Dec 15, 2009
In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?
View 4 Replies
View Related
Feb 7, 2007
A client has a registration workbook for each student which contains 3 types of worksheets, A) Transcript B) Registration and C) Grade sheets. Each file has only 1 Transcript sheet and 1 to many pairs of Registration and Grade sheets.
I have a GPA user defined function which obviously can only be used on a Grade sheet. I would like to return an Error if the user trys to use the udf on a non Grade sheet.
Currently I am just returning 1/0 but the smart tag returns #Name. Can I control the text displayed somehow?
View 9 Replies
View Related
Sep 20, 2007
I'm currently working on a program in which some sort of recursive looping seems logical.
The relevant part of the code I've currently written looks like this:
Sub hello()
Dim Max_stream As Integer
Dim j, jj As Integer
Dim P_sum, P_min As Double
Dim antal_in, antal_ut, rad_in, rad_ut, ut1, ut2 As Integer
Application. ScreenUpdating = False
Max_stream = WorksheetFunction.Max(Worksheets("Data"). Range("B65536").End(xlUp).Value)
For j = 1 To Max_stream
The problem with the code above is the last If, in which the function "rekursivloop" calls itself. I don't know if this type of formulation is correct, but for the cases the If condition is TRUE (ie ut1 <> ""), the function does not return any answer. Does anyone see an error in the logic or have some example for how to write similar recursive loops?
View 8 Replies
View Related
Sep 29, 2012
I am currently using the Match and index functions to search for specific criteria and return specific data based on that criteria. The problem is the formula is returning the first value at the beginning of the month rather then the end of the month value that I am asking for. I have my criteria set to zero so it should be a perfect match. with the live worksheet I have several tabs for different years. I want to take the end of month balance on each of those year tabs for each month in each fiscal year and add them together. My formula only has the one month because I am unable to get it to work much less adding in all of the years information. I have the employees choosing EOM for the last transaction posted during that month and that calculates the month number in the field next to it. I was thinking I could use the match to look for a specific month in that column and return the balance adjacent to it at that point.
View 7 Replies
View Related
