I am trying to use an IF formula for finding specific dates, the problem I encounter is that the date in the sheet I am trying to reference is Apr-08. When I do the COUNTIF formula the only way it will work is if I enter
=COUNTIF('Won or Lost Hidden'!$I$4:$I$145,"4/1/2008")
When I try to enter my IF formula
=IF(ROWS($A$3:$A$3)>$A3,"",INDEX('Won or Lost Hidden'!A$4:A$145,SMALL(IF('Won or Lost Hidden'!$I$4:$I$145="4/1/2008",ROW('Won or Lost Hidden'!A$4:A$145)-ROW($C$5)+1),ROWS($A$3:$A3))))
I cannot use the date Apr-08 or 4/1/2008 or any other date for that matter. I have tried using the serial number and so on... I am stumped.
I have attached the workbook so you can see what I am talking about.
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
Let'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)
The formula looks at the chosen start date (AL260), and then uses a displayed text day (AP20) to display the relvent date to the text day, this works fine however if the start date is say 08/08/14 (a Friday) when the formula detects a Monday (this copies down a range looking at the start date and the text days to produce dates on each line, the formula displays the Monday dates prior to start date, is there any way to alter this to make it display the dates ahead of the chosen start date?
I was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
What I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.
Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.
I’m trying to resolve this issue and would appreciate some help.
Scenario I have a tracking sheet that tracks development of work to be completed by individuals. I have a due date column that shows when the work should be completed. Once the work has been completed, the user enters his completion date.
I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.
Problem When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.
How can I eliminate this being shown as it throws out my average development day calculation?
I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?
way to have the vehicle registration date compared to todays date and have it return either due next month, due or over due.
Todays date is in format month/day/year, registration is only by month/year. Todays date is in fixed location in worksheet updated by function Today()
Basically a formula that resembles =If(registration date < todays date, overdue, If( registration date = todays date, due, If(registration date = todays date+1month, next month, If( registration date > todays date + 1 month, 0))))
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?
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:
BTW all dates are USA standard (m/d/y)
A1 = 12/1/2008 Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....
=date(year(a1),month(a1)+3,day(1)
that seems a bit "Clunky" to me anyone have a better idea?
do not tell me "=a1+30" or "31" because that does not work universally.
How can I have an if statement or other type of formula look at a date and add 26 months and determine if it's 26 months old yet.
example assuming that November 4, 2005 is one day from being 26 months as of today That cell next to it would say "Available" Then the next it would say "Retired" because it hit the 26 month old period.
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
Is there a way to end a formula by using a date? I have a workbook that contains a page for every month I would like for the formulas for march to work until April 1st and then just keep the number value that is there. I don't know if this makes any sense or not but let me know if you need any more details.
I need to have a monthly figure which changes often populate into a cell for each month and then not get overwritten when the sheet is updated .The changing data is being put into cell F3 automatically and I want to put it into different cells monthly so I will have a running record .
I can do something like this in each target cell #=IF(TODAY()=39942,F3,) where "39942" corresponds to a certain date and will be different in each cell but the problem is that when the sheet updates on a new date the previous cells data changes since the statement is no longer true. Is there a way to have the cell only calculate on a certain date and then not change when the date is false ?
Thu1/01/200923Sum of MondaysFri2/01/200923Sat3/01/2009567Sun4/01/200965Mon5/01/20097Tue6/01/200966Wed7/01/20098Thu8/01/200978Fri9/01/20097Sat10/01/200955Sun11/01/2009656Mon12/01/20097Tue13/01/200965Wed14/01/200967Thu15/01/20098Fri16/01/20095Sat17/01/200945Sun18/01/2009987Mon19/01/20095Tue20/01/20096Wed21/01/20097Thu22/01/200968Fri23/01/20096Sat24/01/20098Sun25/01/20096Mon26/01/200947Tue27/01/200967Wed28/01/20094Thu29/01/200976Fri30/01/20096Sat31/01/2009879 This is my spreedsheet. Note. A1=B1 with a custom format of ddd. I would like to have the Sum of Mondays in E1.
I am trying sum if, but can not get it to recognise column A as text.
In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.
Cell C2 has the date - "3/03/2014" Cell F2 has the time - "12:43 AM"
I have combined these in cell O2 - "3/03/14 0:43"
In another sheet 'ref' I have the days split in to two shifts (two 12 hour shifts - from 6am to 6pm (L2), and 6pm to 6am (M2), and the shift that the date falls on is in N2.
So if C2 = "3/03/2014" & F2 = "12:43 AM", it falls between 2/03/2014 6pm and 3/03/2014 6am, then I want to return the third columns value (which shift it is) in the ref sheet. I think this requires Vlookup.
the formula was taken the date from one cell and taking the date from another cell and given a total number of days that it took that person to complete their task and displaying that number in another cell.
I have start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.
Examples
If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed
If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed
If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale
this is my first post and i was a little unsure as to whether to put this in the General or VB/Macros forum, because it kind of involves both.
i'm trying to write a macro that inserts a formula that uses the date of the day that it was run (that is, i don't want it to be volatile like TODAY() and NOW(), but i don't want to have to manually type in the date into each formula).
is there a way that i can write a formula that uses the date of the day it is entered into the cell, or write a macro that adds today's date (perhaps using ActiveCell.Value = Date) and then writes a formula around that?
