I have 3 columns (Name, Pass Date, Fail Date) Each name will only have 1 date in either column B or C.
I want a formula that will say "if there is a date in the pass column (B) then this name is a pass, if there is a date in the Fail column (C) then this name is a Fail"
I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.
It ends up being 18 variations so my IF formula just won't compute.
This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:
IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc
Any way I can get this to work for all 18 possible outcomes??
I have in column A the units, and in B and C the date changes. I want, when I pick a month from the box, that it only picks the IN and OUT date that applies to the month that I picked (per unit). For ex: if I choose Month march, it should show only for unit 19902506 for example: A2 and A5. And not A7 and A11. As March falls in between the Start and End Date. Is that passible? Something with OFFSET? I managed to find the latest known date with the formula =lookup (Column M:N) but Im not sure
View 2 Replies View Relatedlook at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I have a column filled with dates in DD/MM/YYYY format. They go back to 2006.
I want to filter all dates from last monday onwards how do you do this?
I've a table of historical members status list. with this table, I could track each member status history, since the beginning they become a member.
Here is the short of table
Name
Member Status
Date
Adel
New Member
1-Jan-14
[Code] ....
I expect to use Pivot table to show how many people which are still "New Member" and/or "Junior Member" up to now. From the table above, there should be: only one person who still as New Member. because it is only Smith, and only two people with Junior Member. they are Adel and Jhon.
I've search around and found the following useful link, viewing only the last date in a pivot table for each user
also A quick way to return the latest date in a subset in Excel
How to know the last status of each user (each member in my case).
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:
If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008
Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.
I'm trying to create an excel worksheet as follows:
Column1 Column2 Column3 Column4 Column5
Date Time In Time Out Hours Worked Pay
01/03/2000
01/04/2000
01/05/2000
.
.
11/11/2008
I want to insert in the first column (Column1) dates starting from 01/01/2000 all the way up to today, 11/11/2008. So I would end up with a unique date on each row.
To accomplish this manually would be a daunting task. I'm a newbie to excel and don't know how to use any in-built functions.
I'm attempting to write a formula that will allow me to determine the number of days between two dates but to leave the cell with a zero in it if either of the first two date cells are blank or have N/A in them. Here's what I have so far:
=IF(AND(K5="N/A",L5="N/A"),0,L5-K5)
This will give me a zero in the cell if both date cells are blank or will give me the days between if not. How do I get it to also put a zero in the cell if only one of the date cells is blank?
How can I show the year in column M from a complete date in column L?
View 9 Replies View RelatedHow can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample
View 4 Replies View RelatedIn a worksheet, how do I convert a column of dates into a column of each date's numeric value?
View 7 Replies View RelatedI m writing this formula in a cell but i dont receive the result i want...
Formula used... ="Program "&""&G2
(G2 is a Date)
This is te result i get...Program 40210
This is the result i want...Program 1-Feb-2010
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx‎
I have a spreadsheet with two colums with dates. The names of the ranges are 'DataContractualStartDate' and 'DataContractualEndDate'.
How do I manage to count the no. of records with equal dates at the same row?
I have used the following but this gives me zero, while in the example below the result should be 3.
=COUNTIFS(DataContractualStartDate,DataContractualEndDate)
The table looks as follows:
dContractualStartDate
dContractualEndDate
01-05-2012
01-05-2012
[Code] ....
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?
I have a Sheet1 that contains 900 rows of information 14 columns wide (C7:P7 are headers). Column M has an important date. The information is constantly changing.
On Sheet2, I want to display the upcoming dates, in numerical order for each row that's within 5 days of today's date. After the date has passed, it no longer needs to display on Sheet2.
I have columns in my spreadsheet that will be getting updated periodically with a number. 1 week it might be 24, the next it might be 26.... and they would go in January's price, February's price....
The problem is, if a price is entered into the column, we don't know when it was entered other than the fact that it was entered in that month.
Is there a way to show the date and time of when a number gets entered or edited in a cell? This date/time could perhaps show up in a column right next to it.
I have some more questions about excel formula. In one column I have data appearing, as either a 1 or 2, at random as I input it. Each column has around 120 rows. In the row at the bottom I created a formula to calculate how often, on average, the data appears in that column. 1st how do I get each row to represent a date. 2nd how can I get the sheet to show the next expected date that the data may appear in that column, taking into account the date it last appeared and the average between appearances?
View 9 Replies View RelatedSheet1 AB120091002Valid220090702Valid320100702Not Valid Excel tables to the web >> Excel Jeanie HTML 4
In this A column Shows Year month Date format I need VBA Coding.
If A column is Grater than Today date Means B Column States Not Valid. Else Valid.
how i can fix the formula below to show the number of remaining days between the given date and the current date.
View 3 Replies View RelatedHow would I count unique dates associated with another specific value on a worksheet?
For instance, Lets say I want to count each day a particular person makes a sale.
Example:
Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014
So on another worksheet, I'd have:
Sales Person Days with Sale
Don 3
Jack 1
Mike 2
The Formula would go into the "Days with Sale" Column.
I have a pivot table with the column fields as dates. I will like to be able to use VB to dictate which value to show. Here is brief code in which I just recorded...
Sub Button1_Click()
Range("B7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.PivotItems("10/30/2006").Visible = False
.PivotItems("10/31/2006").Visible = True
End With
End Sub
I would like to show current day only using =today() or something similar. THis would eliminate the user having to modify the pivot tables daily.
I have a column of dates listed at the bottom of this post. I am looking to have a formula in a cell that gives me the earliest date from the column. I have tried Min() and Small() with no success. Is it perhaps the "AM" and "PM" that is throwing things off? A small example of the data:
11/21/2008 AM11/09/2008 AM11/09/2008 PM11/09/2008 PM11/10/2008 PM11/11/2008 AM11/11/2008 AM
and it should return 11/09/2008. Thanks in advance any and all.
Ed
I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.
Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918
2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009
Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.
Excel. Let's say this how a column looks like:
a
b
b
c
d
Is there a formula to get this outcome:
a 1
b 2
c 1
d 1
If not, is there a way to create a list of words that are in a column?
formula to input to excel to get the dates rolling down in A cell i want it to go A1(1/1/2014) A2(1/1/2014) then a3(2/1/2014) a4(2/1/2014) a5(3/1/2014) a6(3/1/2014) i have a lot of these dates to put into excel
View 5 Replies View RelatedLet's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
I'm having where basically I've got the below scenario on a spreadsheet:
Start Date - 14/03/2013 (Cell A2)
I want to look up this date against a 52 week structure to see where it is in our "working calendar year". I've got a table which has the following fields:
Week Start (Cell E2) - E.G 11/03/2013
Week End - (Cell F2) - E.G 18/03/2013
Week Title - (Cell G2) - Week 1
This table follows the same format for all 52 weeks.
Now in this example "Start Date" falls between the "Week Start" and "Week End" date so I want to output Week 1 in cell B2. However I could have a "Start Date" of 29/03/2013 that falls in week 3 of the working calendar.