Completing IF Function With Date And Time As Inputs?
Jan 16, 2014
Using the attached doc, and its legend tab, I need the following columns corrected as I am seriously tripping up on formats vs inputs for outputs.
1.) Column "N's" result based on the duration output of the preceding column "M" is:
<=4 = 40
5 = 30
6 = 20
>=7 = 10
2.) Column "Q's" result based on the duration output of the preceding column "P" is:
<=18 == 40
>=19<=24 == 30
>=25<=48 == 20
>=49 == 10
The legend tab shows the info as well.
View 3 Replies
ADVERTISEMENT
Mar 10, 2009
why this returns a #Value! error?
{=RANK(MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)),IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1),1)}
I just used the even row selector as an example.
MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)) returns 203 and IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0}
when you press F9.
If I change the range to exclude the non-numeric value I get the same error (it shouldn't matter according to the help file as non-numeric values are ignored).
View 9 Replies
View Related
Mar 23, 2009
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.
We have example P/N:
1234567890123
12345678
123456
should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"
Is there a function that inputs a cerain number of a specific character ("space" in this example)?
View 2 Replies
View Related
Nov 16, 2007
I want to build something like this....
function getdb(a,b,c,d,e,f........x)
getdb = a & "," & b & "," & c &....& x
end function
where x is the total number of variables.
So if in excel, I run getdb (a,b) that it will know that the funciton has only 2 variables but if i run getdb(a,b,c,d,e,f,g,h,i,j) that it knows.
I am trying to avoid getdb(a,b,,,,,,,,,,,,,,,,,,,) as I bet the user will not know how many ","s to use
View 9 Replies
View Related
Mar 11, 2014
I'm trying to compose a date based on inputs from a userform. The user selects the day, month and year in combo boxes, and I want to place this data on a single cell in the format DD/MM/YYYY. How can I do this?
View 1 Replies
View Related
Oct 6, 2009
I have a working week that starts at 07:00 on a Monday morning and finishes at 14:30 on a Friday afternoon.
For planning purposes I need to know at any given point how many working hours are left. The reason for this is so that I can multiply that figure by the amount of Engineers available which will give me the amount of Man Hrs left in the week.
For example - if it is 11:00am on the Tuesday, how many working hours are left ....
View 13 Replies
View Related
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
Dec 17, 2008
I m Trying to add some features to it and run into an issue. Here are the fields we are working with!
A1= 13:30:00 17/12/2008
(in other words 1:30pm on date given) - Need to know proper cell number format!!
B1= 23:00:00
(this represents 23 hours) - format for cell is [h]:mm:ss
C1= Unknown function
(This field is my problem!!)
Answer here SHOULD be "14:30:00 16/12/2008"
I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
View 4 Replies
View Related
Feb 8, 2009
I need the code to add the selected date to a specific cell lets say cell D8
at the moment it adds the date in any cell selected
View 4 Replies
View Related
Feb 16, 2010
Ihave this exel sheet i nedd function to write the starting date and time immediatly after i enter the name in the last colume ineed from exel to write the date and time emmideatly if the case is (done, cancelled ,or rejecteted)if the case is (select status )i want the cell empty but if the case is (pending)iwant to the program to calculte the deffirence between the ClosingDatetimee(which written by the program)and the delivering date(which entered manually by user)
View 9 Replies
View Related
Feb 13, 2014
I am attempting to find a way to search through a database I created based upon every day of the year. My goal is to have a user input a beginning date and end date for a trip, then my functions will return the range specified with each day and it's individual temperature average (in my database).
I also need generating the dates between the start and end dates that are entered by the user. I was considering combining Days360 with an Hlookup that searches through a massive sheet with all the dates for the upcoming year and then going down 1 in the column for the difference in the dates in each column.
Vlookup Help.xlsx
View 5 Replies
View Related
Sep 25, 2008
I have some data in sheet1 with 10 columns and 5000 rows.
I want to filter the data with 2 criterios.
When I go to 4th column and Click custom filter, I will give one criteria and select "or" and give another criteria. SO I will get the result in sheet1, I need to copy the data and paste the same in sheet 2 with the header.
The problem is, I need to filter more than 20 times giving the criteria and copy the result and paste in sheet 2 one after the other.
So i need a macro to solve this time consuming work.
I just paste the two criteria either in a text box or some cells and run the macro. the macro has to filter the data in sheet1 based on my input.( that is criteria1 or criteria 2) and the result should be pasted in sheet2 with the headers.
Again I delete the values in my input cell, and paste the new values, and run the macro, that result should be pasted after the first result, with the header. (would be great if that is pasted leaving one row above, that is if the first result is pasted in sheet 2 till 10th Row, then the send result should be pasted in 12th row and so on..
the similar kind of question with some changes, I posted in the below link with
http://www.excelforum.com/excel-prog...in-sheet2.html
View 9 Replies
View Related
Jun 17, 2006
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
View 3 Replies
View Related
Mar 16, 2012
I have the following coding that I have tried in both the Sheet code and as a module at different times.
Sub CopyEmployed()
Sheets("Employment").Select
Range("A3:L200").Select
Selection.ClearContents
Range("D4").Select
Sheets("Act1").Select
- And there will be more here when I can get this first part working-
When I step through this works up to "Selection.ClearContents" but then ignores the next two lines
Range("D4").Select
Sheets("Act1").Select
and goes straight to the Private Sub coding below that I am using to change the sheet tab names (This is in the ThisWorkbook section and works perfectly)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LinkedCells As Range
Dim ArrayOfMatchingSheets As Variant
Dim i As Long
Set LinkedCells = Sheets("Menu").Range("E8:E22")
[Code] ....
I can continue to step through this until it eventually returns to
Range("D4").Select
Sheets("Act1").Select
It may then run the remainder of the coding but what I have tried so far produces more Runtime and type mismatch errors which I will post after I can fix this.
Bottom line is I dont want the change sheet tab code run - I need the remainder of the Sub CopyEmployed() to run.
View 9 Replies
View Related
Feb 19, 2009
situation: if cell I22 in Sheet1 = "MFRHTC", a message box will pop up and give the user some info and at the same time ask if this is a Fed Ex shipment
if user selects NO, then nothing will happen
if user selects YES, then the user willl be directed to cell L15 in Sheet2 in order for them to input their ship to address
here is what I have so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("I22"), Target) Is Nothing _
And Target.Value = "MFRHTC" Then
Msg = "Units will provide the following in order to have ammunition Fed Ex to HTC's " & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & " POC" & vbCrLf
Msg = Msg & " Unit ship to Address / CANNOT BE A P.O. BOX" & vbCrLf
Msg = Msg & " Phone Number" & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & "Input the required info in the Fed Ex ship to address Box"
MsgBox Msg, vbInformation, "FED EX AMMO INFO REQUIRED"
If MsgBox("IS THIS A FED EX SHIPMENT REQUEST", vbQuestion + vbYesNo, "SELECT EITHER YES or NO") = vbYes Then
End If
End Sub
View 9 Replies
View Related
Jan 2, 2013
I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
View 4 Replies
View Related
Dec 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Jul 3, 2007
Im using the following code to assign numerical values to words in two columns, then multiplying the values together and painting a cell with a specific color assigned to the final number. The problem is it wont do this past row 19
mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row
For m = mLastRow To 5 Step -1
' assign numerical values to the words in these columns
e = 22
r = 23
' Give Extent a value
If Cells(m, 9) = "Minor" Then
Cells(m, e) = 1
End If
If Cells(m, 9) = "Moderate" Then
Cells(m, e) = 5
End If
If Cells(m, 9) = "Serious" Then
Cells(m, e) = 8
End If
If Cells(m, 9) = "Critical" Then
Cells(m, e) = 10
End If....................................
View 4 Replies
View Related
Oct 21, 2009
We currently use Excel to make reports of data obtained in field surveys. General data is repetitive throughout report. We have a title page, general particulars (GP) page, table of contents then data table pages. Each data page has info from general particulars page. i.e project name, date, inspector etc. Right now some cells of data table pagess have formulars pointing to applicble cells on GP page.
Presently I am designing a userform to input title and GP page data. So far OK.
Question: Can I take input on userform and add to footer. Info to be added after or inserted within text already there? i.e. Surveyors Name: ?(left footer) something center and something right. What type of code needs to be added and where do I place it .
View 9 Replies
View Related
Oct 24, 2007
I have a column of values resulting from subtracting a static date and time from the current date and time.
This means it is constantly updating, which makes it impossible to sort.
All my work depends on sorting those values, though.
View 12 Replies
View Related
Feb 21, 2013
I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:
01/01/13 00001
No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?
View 3 Replies
View Related
Apr 22, 2008
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
View 2 Replies
View Related
Jun 24, 2013
I have to type in a long place name with 10 letters repeatedly and whereas before Excel completed it after I typed the first two letters it has now stopped doing so. Is there a way to reactivate this facility? Other names do get auto-completed when typed in the same column.
View 3 Replies
View Related
Feb 5, 2007
I have got Names in column A and Join_Date in column B. In column C I want those whose Probation period is going to finish this week. Probation is of 90 days.
View 9 Replies
View Related
Jul 19, 2014
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
View 7 Replies
View Related
Mar 28, 2014
I am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 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 17, 2005
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
View 13 Replies
View Related
Oct 20, 2009
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
View 5 Replies
View Related
Nov 19, 2008
I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:
=((I12/100)*25)/D12
After 1pm I would like the formula to be
=((I12/100)*25)/B12
I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
View 6 Replies
View Related
