Got a random problem with the IsDate function and was wondering if anybody could help?
Below is the code I am using to check a user input of a date, and if it is a recognised date put it in to a standard format (dd/mm/yyyy). The problem is that VBA is checking for an American ...
I'm currently running a macro which copies data from one sheet and pastes it to another.
One of the fields on the primary sheet is a date in the english format DD/MM/YY but when it pastes onto the second sheet it becomes a Julian date 39820 instead of 01/07/09, and when this figure is converted back to date format it becomes american format MM/DD/YY
Currently I've set the macro to PasteSpecial xlValue
How can I make it paste into English format?
I'm using excel 2003
I have the following:
Private Sub TextBox1_AfterUpdate()
'//Check for valid date
If Not IsDate(TextBox1) Then
Me.TextBox1 = ""
'Show SplashForm2 if not date
SplashForm2.Show
Me.TextBox1.SetFocus
End If
End Sub
I thought this was working but I put 22/07 in TextBox1 and stepping through it it goes from the "If Not statement" to the "End If"
Don't know why it is not recognizing that it is not a date. It is missing the back slash.
I am wanting to add some data validation to my excel form. It is meant to be a check on date.
View 4 Replies View RelatedI have US reporting package that e-mails a report, which I then run macros to put extract the required data, and pivot etc...
There can be anwhere between 1 and 15,000 lines of data.
The file is generated as a CSV file, with dates in the first column in the format mm/dd/yyyy. Other header information also has a "/" so it is impossible to do a text to columns.
I need to continue using UK dating format, so cannot change the Excel settings.
Can anyone provide me with VBA code to convert mm/dd/yyyy into dd-mmm-yyyy without it ignoring any dates where the dd is less than 12?
I have excel 2007 and how to do a text split. For example, in A1 I have : Name American Company AP Code PI Due $0.00
Is there anyway to do a text split in B1 to just have:
American Company
are there any easy way to reformat a fractional number: I need, for example, to make 5,23 look 5.23. Is there any function in Excel? I guess not, but have no clue where to start my VBA code.
View 6 Replies View RelatedI get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.
example
01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.
I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan
There are over a thousand entries so re-typing isn't an option.
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?
I have got a cell (A1) with a Date: 13/10/2008
I want a formula like IsDate(A1) = True
without UDF's
My purpose is to obtain a "thing like" : =SUMPRODUCT(Not(Isdate("MyRange"))*"MyRange")
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
I have a csv file which has been output by an American system that I use, the text that is output is one column is as follows:-
6/19/2007 10:24:44 AM
2/20/2007 11:01:37 AM
2/27/2007 8:18:58 AM
I would like to learn a formula which re-arranges the date to a UK format so that I can do more detailed analysis of data. If possible I would like to strip out the time part of the text in one.
I have these values in my excel sheet
in column A in column B
1/02/2014 1650
2/2/2014 1649
3/2/2014 1648
4/2/2014 1647
5/2/2014 1646
I would like a formula that gives me the value in column B if the date of today is equal to the date in column A.
I've tried with simple IF formulas but it doesn't really work because the formula spans over several rows.
I have a spreadsheet which has a date in a column, day in another and an IF function related but the if function does not seem to be working, can anyone assist? here is an example of how it is setup Cell A1 has a date e.g. 31/01/2009. Cell A2 returns the day of the week function =WEEKDAY(A1,1) formatted to custom to return the DAY of the week using dddd. Cell A3 has the IF function e.g. =IF(A2="Saturday",0.1,0). Cell A3 is the PROBLEM as all this ever returns is 0 (false) and never returns 0.1.
View 2 Replies View RelatedI am trying to figure out what function automaticly updates a date in a cell everytime the workbook is opened.
View 5 Replies View RelatedI've entered there name in column A, and the expiry date in column B. How do i then get column C to show how many days or months are remaining? Ideally i would have the guys with 3months or more left in green 1-2months amber and <1month in red.
View 3 Replies View RelatedI have the following formula in a cell
=LOOKUP(WEEKDAY(A1),D2:D8,C2:C8)&A1
that I want to look up the Day (mon, tue, etc) and then return the date entered in cell A1
So if the date in cell A1 is 01/05/08, the formula should return Thu01/05/08.
Currently it returns Thu39569, even though the cell is formatted as a date format... How do I get it to return the date in a date format?
Is there an easier way to construct the formula in the sales revenue col of the monthly analysis table on the summary tab so that one does not have to enter the specific dates into the formula?!
View 5 Replies View RelatedI have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date. This is what I have so far: =IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C)).
I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in. So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".
I have found threads that explain that if you want a to use the now() or today() function but make it static, you have to put a code in the worksheet relating to it.
I have read quiet a few threads, but i can't seem to make my code work.
I need to format column A to have the date format in it. I have a event procedure that puts Now() when run in Column A and I think i need to add the following code to the worksheet to make it static.
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.
In column A there is a date and time function in the format as follows :
dd/mm/yy hh:mm:ss most of the time.
When this is downloaded it is easy to manuplite. But some times it show as
above but as a text string or sometimes the dd/mm is switched to mm/dd.
Does anyone know the work rounds these formulas?
I use the dd/mm and Hr function to complie reports from column A but usually
spend hours using mid() left() function due to the date change.
I have tried formating the cell but this does not work?
Kindly help me with the attched file.
I am trying to divide the months into weeks so I could easily identify if a particular date of a month falls on 1st,2nd,3rd or 4th week of the month.
How can I take a cell with a date in it (A students birthday) and in another cell automatically show how old that student is? (2/18/03 in cell A1 and show 8 in cell A2)
View 5 Replies View RelatedI have a sub that copies a section of my sheet onto another sheet and then clears out my sheet so I can type in the sales numbers for the next day. Now I am trying to figure out how to get the date to change.
What I would like to do is to have the Macro type the date MM/DD/YYYY into cell C9. The date should be whatever date the computer clock is set to minus 1 (since we are filling out yesterday's sales). The other part that I am struggling with is making B9 appear as the specific day of the week.
The key part of this is, C9 may have the date entered in manually and if so, I would like to be sure that the day of the week matches the date entered if at all possible.
i have date values in say, cells a1 to a4
20080522 21:00:00
20080523 14:45:00
20080523 15:00:00
20080523 15:15:00
i would like cell b to tell me when there is a change of date (not change of time), ie insert a 1 in lets say cell b2. i know it will be a simple if function but i dont know how to only read the first 8 digits
I am trying to format this function to just return the year, but I can't seem to get it to work. It just returns the full date and all I need is the year.
DateAdd("yyyy", -1, Date)
I am comparing two series of data, a daily series and a weekly series, to make calculations. The "F" column is daily data and the "I" is weekly.
The formula I am using is:
=IF(AND( SUM(I58:I62)<F62,I63>F63),B64,0)
The data I need to use in the "F" column in place of "F62" is always on a Friday.
I had taken "F62" which is the preceding working day's data. In fact I need to take the preceding Fridays data in column "F" as I need to compare the week to week trends. Due to bank holidays I cannot count back form F62 to get the data. There is always end of week data recorded on Fridays even though it may be a bank holiday.
I have the dates in the "H" column. Can a date reference be used in the formula to find the Friday data in column "F"? Or is there another solution to this problem?
I have a spreadsheet where columns I through V are date fields (stored as text as some have letters after the date). Is there a way that I could write a formula so that Excel returns the column letter of the last cell without a letter at the end of it? So it kind of looks like this:
I J K L M
05/01/2014 05/15/2014 05/30/2014(p) 06/20/2014(p) (null)
What I want in the column with the formula then would be just the letter J. Is this even do-able?
a column contains dates in date format, contains a date before the column b date b column contains today() I want c column to get some output, if b-a is more than 5 years (exact days), say 20 do , if b-a is more than 2 years and less than 5 years, say 40 do , if b-a is less than 2 years , say 60 otherwise blank use if condition to get the above results. I have formatted a, b with date formats
View 9 Replies View Related