Excel 2013 :: Date Placed In Cell When Text Entered In A2
May 8, 2013Is there a formula which places the date In A5 whenever text is entered in A2 - Excel 2013
View 9 Replies
ADVERTISEMENT
Excel 2013 :: Change Text In Cell Based On Date In Another Cell
Apr 14, 2014I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
Excel 2013 :: Extract Date And Time Info From A Text Cell That Contains Text And Date And Time
Jul 19, 2014I 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.
Excel 2013 :: Check Cell For Text And Return Corresponding Text In Adjacent Cell?
Jan 28, 2014I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
Make Static Date Appear When Cell Has Text Entered?
Jan 19, 2014Any way where is a list of cells (b3:b113) has text entered, or specific text entered, it will enter a static cell in the corresponding "d" column.
I know of CTRL + ; but i don't really know how to get it to work as a macro, can't find a forumla that enters the static date, and i don't want to make a macro to copy + paste special the date when it happens.
So all i should have to do is entered text in column b, then it will add a time stamp, when that text was entered, it doesn't have to update.
Like all i need is this, but instead of the "now()" function, it will have another function that doesn't automatically update?
=IF(ISBLANK(E5)=FALSE,NOW(),"")
Excel 2013 :: Concatenate Header Row Text When Row Cell Is Not Empty
Jun 5, 2014I am trying to concatenate entries into one cell so that when uploaded, the comma-separated contents will be treated as tags. copperberry sample file.xlsxSee attached sample file. Wherever there is a 1 in a row, I want to take the column header text above that 1 and concatenate it with subsequent text in the cell at the end of the row. See sample end result in cell J2. I assume I need an IF statement, but I'm not sure how to phrase it to collect all the concatenations needed. There are 200+ rows.
copperberry
Windows 8.1
Excel 2013
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
Jul 10, 2006I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
Excel 2003 :: Cell Value To Change If Date Is Entered In Different Cell?
Feb 21, 2014I am using Excel 2003 at work.
New cases are entered on to the spreadsheet. The case remains open until a closure date is entered, at which point the case is closed. What I need is the following:
Cell A3 = should say "Open" if there is no value in cell Y3
Cell A3 = should say "Closed" if a date or any value is entered in cell Y3. Ideally it should only say "Closed" if a date is entered in format xx/xx/xx, but am flexible so that it says "Closed" if any data is input.
At the moment Cell A1 has a drop down list consisting of open and closed. I will remove this if it causes complication.
Stop Count Of Days When Date Is Entered On Specific Cell - Excel
Mar 17, 2014excel.jpg
how to make my network days stop counting when I insert a value in the completed column (in this case H3). I have a Date Received column (A3), a due day column (G3) and the last column with the date (N3). My remaining days column keeps counting after I filled the completed cell (H3) and won't stop counting after. I need a formula to stop after the completed cell is fill up.
Static Date Stamp: Add A Date Stamp That Will Populate A Field When Text Is Entered Into Another Cell
Jun 28, 2007How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies View RelatedExcel 2013 :: Making Numbers Display With Color Based On Text Inputted In Different Cell?
Nov 20, 2013EXPENSE MASTER 2013 sample color.xlsx
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
Excel 2013 :: Copy Entire Row If Cell Contains Specific Text And Paste Values Into Another Worksheet
Jun 23, 2014I am using the code below in Excel 2013.
Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then
[Code]....
This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.
Excel 2003 :: Date Formula For If Given Date Is Before Or After Today Or If Nothing Entered
Jan 17, 2013Cell B1 contains a date, then B2 contains a formula that says:
=IF(A1>TODAY(),A1,A2)
A1 contains a green tick and A2 contains a red cross.
What I am trying to add is that if B1 contains no date then B2 needs to be blank.
I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.
I am using Excel 2003.
Excel 2013 :: Set Date So That It Changes Red After That Date Passes?
Jan 30, 2014I need to set the dates on a tracking sheet to turn red after that date passes, I have tried using conditional formatting - Highlight cell rules - Greater than, but this hasn't worked.
It is excel 2013,
Excel 2013 :: How To Add Date Range
Mar 4, 2014I have a big spreadsheet with IT / Finance data. Below is the formula, I currently have. I am summing column Y in Tab Savings Data Detail, where column A does not equal Parent, and The Value in Column AK on the Savings data details tab matches the value in current tab for cell B26 and where column G on the savings detail tab equals Transition to IT. I am adding that value to the second sumifs statement essentially the values in column X on the savings detail tab with the same criteria.
Now I need to add to each of these formulas. I need to sum only the values where the date value in column W on the data details tab is greater than or equal to 01/01/2013 and less than or equal to 12/31/2013
find the easiest way?
=SUMIFS('savings data details'!$Y:$Y,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")+
SUMIFS('savings data details'!$X:$X,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")
Excel 2013 :: Date Stamp Modified Row
Apr 3, 2014Looking to add automatic date stamp to a column of cells each time their corresponding row or specific cell in that row is modified.
I'm using Excel 2013.
Excel 2013 :: Placing Date Value To 3 Different Groups
Jul 11, 2014I am rather new to excel and currently using excel 2013.
I have a worksheet with column A ( data group) Column B ( Sent date with MM/DD/YY) , Column C Aged Group {using this formula =IF(B2="","",(TODAY()-B2))} , Column D status ( if the record is open or close).
I would like to create a table that can segregate the data group and how many days it has already aged. And then group them with different aged group. Refer to the attached screenshot.
From:-
Data GroupSent Date AgedStatus
Quotation 06/06/1435Open
Invoice 07/11/140Open
invoice 05/01/1471Open
PO 07/01/1410Open
PO 07/05/146Open
SO 06/30/1411Open
Quotation 05/18/1454Open
SO 05/05/1467Open
Cash Note 07/03/148Close
To:-
Data Group0-15 15-3030 above
Quotation
Invoice
PO
SO
Cash Note
testtest.xlsx
Excel 2013 :: Date On Horizontal Axis On Chart
Apr 12, 2014I'm having trouble getting the dates i want on the horizontal axis on a chart in Excel 2013. I want 12 dates along the axis starting at 01/01/2013 then each month until 1/12/13 however as you can see the dates are 01/01/1900.
View 10 Replies View RelatedExcel 2013 :: Can't Find Date And Time Picker
Nov 21, 2013I just installed Office 2013 Pro. In my Office 2010 Additional Controls toolbox, the control toolbox has a Date and Time Picker(DTPicker1) . I cannot find it in the Additional Controls toolbox in Excel 2013. I certainly hope they did not choose to not make it available in Excel 2013 - this is an extremely important control for my Excel spreadsheets as it allows inputting a date range on a userform with DTPicker1 and DTPicker2 and generating reports using VBA in conjunction with Autofiltering code on a Generate Report button.
View 8 Replies View RelatedExcel 2013 :: Simple Date Arithmetic - Circular Reference?
Aug 10, 2014Here is a very simple workbook/sheet with some simple date arithmetic.
I keep getting the warning that there's a circular reference in C2.
Either I need to suppress the warning so vba loops can run or PREFERABLY I'd get rid of the "circular reference".
Run with Excel 2013.
Excel 2013 :: Select Date Based On Userform Selection?
Apr 5, 2014I am working with DatePicker calendar. I have two userform with date entries. In one userform I will have 2 textbox to enter dates. In the other one I will have 4 dates to enter. I used to have Excel 2007 with Calendar Control and used to work fine but had to make multiple calendar to control individual Textbox in multiple Userforms.
I am looking for a way to, If a userform is selected to trigger only the textbox name that is within that userform. If I add more entries the code adds automatically the date from the previous userform in the next one.
[Code].....
I was thinking that this would work but it still adds the date to my frmdashboard userform if I am in my frmdata userform.
Is there a way to trigger only the userform that I am currently in?
Using Excel 2013.
Note: I also have a "Clear All" VB Code that will delete every entry in my userform on Initiate but I am looking to not go this path.
Excel 2013 :: Counting Text Occurrences In A Column?
Aug 15, 2014I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
Text Search :: Find A Match When Date Entered
Jan 29, 2006Here is what I have:
Column J is the date opened. Column T is the date closed. Of course,
column T will have no date if still open.
Column K and O may or may not have text.
Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the search.
What I need is a formula in AF8 that will look at the date entered in AF6 and find a match in columns J and T. Then it will look to see if there is text in K and O. If so, populate AF8 with that text.
Excel 2013 :: Format Date / Time And Macro To Run On Opening Orkbook
Feb 2, 2013I need to place a date and time in a cell 14/9/2013 6:00 PM
Tried several formats under custom format without success.
Also need addition to following macro to enable it to start on opening of workbook
Code:
Sub Macro1()
Do
PauseTime = 1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
[Code]...
Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date
May 19, 2009I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
VBA - Validate Date Entered In Input Box Is Month Ahead Of Date In Cell
Aug 14, 2012I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
Add A Date Time Stamp In A Cell After Data Is Entered In A Different Cell And Date
Apr 18, 2014I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?
View 9 Replies View RelatedExcel 2007 :: Cannot Change Date Format On Data Already Entered
Jun 22, 2014I have a SS of a WW1 casualty list and one of the columns is Dates of Birth but the data has been entered in three different formats eg 21/2/1898 or 21.2.1898 or 21 Feb 1898. I have tried to Format Cells and change the date format to the slash but after highlighting a number of fields and right clicking they will not re format. Though i use Excel quite a bit i am a general user and dont understand it in depth.
article using =DATEVALUE(
am using Office 2007.
Comparing Previously Entered Date With New Date In Same Cell?
Apr 15, 2014I'm working on the final stage of a project. I'm attempting to write code that will set off a chain of events if two dates are 91+ days apart from each other . I've attached a sample worksheet that shows the bare bones basics of what I'm attempting to do.
What will be the most efficient and effective way of accomplishing my goal: userform, functions, formatting, etc.? T
Excel 2013 :: Cell Change Animation
Mar 27, 2013In Excel 2013, there is a transition in the cell when values change. I have a DDE feed updating data every few seconds and the folding animation is distracting. Is there a way to turn this off?
View 5 Replies View Related
