I want a formula to return the last day of the month for a given date in cell C9.
All over the web I see this recommendation:
=DATE(YEAR(C9),MONTH(C9)+1,0)
But every time I use it I get the error #NUM!
I've tried formatting the cell different ways, nothing works. The date in C9 is a valid date, Jan-09, which is actually 1/1/2009.
I have an issues with a formulae I have written to measure two factors;
A; How many different countries appear within a certain date range. B; What countries are they.
I tried a unique list, but could not get it to work within a range? I dont particuarly care how the data is pulled through as long as I do it. At the moment I have become stuck in 'Unique Ranges' and have n given up?
The formulae issue is in the tab "Problem_Formulae", the dat in the sheet 1, and the date ranges in sheet 2. Also, just to make life a bit more challenging pivot tables are a no go .
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.
View 2 Replies View RelatedI'm trying to copy an ELOOKUP formula down a column in a sheet. The formula is losing part of the table array when I carry it down and returning an N/A error. Interestingly, it will work when I copy it across a row.
View 9 Replies View RelatedI'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.
I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6
I'm trying to write a macro that will insert a excel formula into a specific cell. When I try to run the macro I receive a compile/ syntax error. I don't understand why as the formula works in excel. Here is the code (formula only)
View 4 Replies View RelatedI am having a code like :
Code:
ActiveSheet.Select
Dim lFeild_Num As Integer
With Selection
lFeild_Num = .Columns(13).Column - .Columns(1).Column + 1
End With
Application.Dialogs(xlDialogFilter).Show lFeild_Num, ">=", 1, "
I have 2 columns of dates and would like to find the difference in 2 dates in column a and b for example. when I use the formula a2-b2 I get #Value! or # Name? in these cells. The cells appear to be in format dd/mm/yyyy. I tried formatting them to mm/dd/yyyy, but the dates are not changing at all. I have quite a few of these spreadsheets so it won't be practical to retype each cell individually.
View 7 Replies View RelatedI am facing a weird date format error in .csv file for a specific time periods.
I have a file where data is stored on monthly basis from the years 1976 uptil today. I have all the data months in expected format except for the years 1989, 1990 and 1991. These files are created by using Informatica and SAS.
For example:
Expected date format = 1-January 1989
Actual Date Format = 1-Jan-89
This issue doesn't exist when we open the same file in Excel, Textpad and Notepad.
I have attached screenshot of the data (filename: CSV Issue.JPG).
I haven't had issues with vlookup on other worksheets, but this one is giving me trouble. I have attached the excel file for your consideration.
The lookup value is in cell U2, formatted as a date
The table array is V2:Z19
The column index number is 2 for C3
False lookup
I've tried the formula with and without the text function for the date and I've also tried different formats for the dates in columns V to Z because I know the format of the cells have to match for vlookup. It is very frustrating because this should be simple, but it is messing up.
I have to excel files
1. Temperature & Humidity Reading
2. Summary
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)
Cell 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.
I found the following code someware on the forum and have been using it for a while and it has been all good, however when I went to use it today it is coming up with Runtime error 1004 and highlights Range("H1") =MyDate.
View 2 Replies View RelatedHere is the data I am working with. On another sheet, I want to count the number of entries on Total requests sheet if Column M - Closed < Column Q - End Date. There is other criteria and I have this, which doesn't work: Run-time error '1004' Application defined or object defined error. I think it is due to the part where I'm working with the dates...
======================
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('Total Requests'!R2C3:R" & tot_req_row & "C3,RC1,'Total Requests'!
R2C10:R" & tot_req_row & "C10,""5-Very Low"",'Total Requests'!R2C13',""
I developed a program which uses Date function in several places. Problem is that on some machine this function is not working and I'm getting compile error.
What is the reason for that. Is this a system setting??
I have a formula
=AL260-WEEKDAY(AL260,2)+MATCH(AP260,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)
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.
Example: 04/31/08 - 04/01/08 = 30 (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.
View 7 Replies View Relatedwhat formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.
View 3 Replies View RelatedWhat 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.
The formula I have got to work is
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)
but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER
when I change the formula to
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)
The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?
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?
=SUMPRODUCT(--(wc_date_of_loss&A3))
=SUMPRODUCT(--(wc_date_of_loss=A3))
=SUMPRODUCT(--(wc_date_of_loss=TEXT(A2&A1,"general,dd/mm/yyyy")))
I have a spreadsheet with a column full of dates
I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.
Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..
There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!
I have to generate a report using a pivot table, that tells me what work was done on time and what was late. Entering a formula outside the pivot table sees the scheduled start date drop downs as "blank" and says they are late when they are not. I cannot seem to use the value fields when I try to create a calculation field within the table. I use 2010 at work, 2007 at home.
sep 11-17
FGHIJK10Count of WO No.
=IF(G12>=H12, I12," ")=IF(G12
I need a formula thet will look at the last date of the absence and calculate what would be the last working date in the given financial yaer.
Example
Abcence Start Date 21/08/13
Abcence Due End Date 04/11/14
Financial Year Start Date 01/07/13
Financial Year End Date 30/06/14
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 trying to collect data that is true to 2 criteria from sheets in a workbook the formula i am trying is =SUMIFS("Week"&ROW(1:1)&"!F436:L436","Week"&ROW(1:1)&"!F6:L6","=9001","Week"&ROW(1:1)&"!F7:L7","=230 108000").
View 11 Replies View Relatedwhy I am getting a #Value! error with this formula? When the code highlighted in red is not included then the formula works just fine! But as soon as I add the (red) code I get the #Value! error.
=IF('Time Sheet'!D9="Instruct",IF('Time Sheet'!L9="Student",VLOOKUP('Time Sheet'!E9,Rates!A1:G40,4,D5:D40),IF('Time Sheet'!L9="Day",VLOOKUP('Time Sheet'!E9,Rates!A1:G40,5,E5:E40))),IF('Time Sheet'!D9="Field",IF('Time Sheet'!L9="Hourly",Rates!K2,IF('Time Sheet'!L9="Day",Rates!J2)))),IF('Time Sheet'!D9="Dispatch",IF('Time Sheet'!L9="hourly",Rates!K3,IF('Time Sheet'!L9="Day",Rates!J3)))
I am attempting to use the formulas which I have learned but this time I keep getting "The formula you have typed contains an error" message.
=IF('Time Sheet'!K3:L3="Contractor",IF('Time Sheet'!F9*(VLOOKUP,'Time Sheet'!E9,Rates!A1:G40,4,false)<(VLOOKUP,'Time Sheet'!E9,Rates!A1:G40,2,false),(VLOOKUP,E9,Rates!A1:G40,2,false),if(F9*(VLOOKUP,E9,Rates!A1:G40,4,f alse)>(VLOOKUP,E9,Rates!A1:g40,3,false),(VLOOKUP,E9,Rates!A1:G40,3,False),(Vlookup,E9,Rates!A1:G40,4 ,false)*F9)))