Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
I have a macro which AutoFilters a date column. The spreadsheet is now
being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
Does it not work then. I ask because if the dates are just formatted
differently, the underlying date values are the same. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I have a macro which AutoFilters a date column. The spreadsheet is now being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
No, Bob, it doesn't work. Not only that, but I've been trying desperately
to do everything I can to make it work. I made a copy of the spreadsheet and changed the format of all these cells to DATE as yyyymmdd. That gave them the correct look, but they wouldn't filter with "greater than or equal to" - but they WILL filter on "equal to". So I copied the entire column into Word as a single-column table, the repasted back into Excel, just to have everything as pure text. Then I formatted the cells as NUMBER with no decimals. Same thing: filters on "equals" but not "greater than". The only thing I can think of is that I have changed the way I create this spreadsheet. The previous versions (in which the filter worked fine) were built from a database query table opened in Excel. The same column there is also formatted NUMBER, no decimals. But the numbers came out of the database as "20030202". This sheet, though, is built by searching a text doc for strings and inserting them into the cell as "Cell(r,c).Value="string". Doing this, the search finds the date field "02 Feb 2002" and writes it into the cell, where Excel automatically formats it as a DATE, showing "02-Feb-02", but yielding the date value in the formula bar. Can you give me any hints? Ed "Bob Phillips" wrote in message ... Does it not work then. I ask because if the dates are just formatted differently, the underlying date values are the same. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I have a macro which AutoFilters a date column. The spreadsheet is now being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
Ed,
Try changing the way the data is retrieved to Cell(r,c).Value=CDate("string") -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... No, Bob, it doesn't work. Not only that, but I've been trying desperately to do everything I can to make it work. I made a copy of the spreadsheet and changed the format of all these cells to DATE as yyyymmdd. That gave them the correct look, but they wouldn't filter with "greater than or equal to" - but they WILL filter on "equal to". So I copied the entire column into Word as a single-column table, the repasted back into Excel, just to have everything as pure text. Then I formatted the cells as NUMBER with no decimals. Same thing: filters on "equals" but not "greater than". The only thing I can think of is that I have changed the way I create this spreadsheet. The previous versions (in which the filter worked fine) were built from a database query table opened in Excel. The same column there is also formatted NUMBER, no decimals. But the numbers came out of the database as "20030202". This sheet, though, is built by searching a text doc for strings and inserting them into the cell as "Cell(r,c).Value="string". Doing this, the search finds the date field "02 Feb 2002" and writes it into the cell, where Excel automatically formats it as a DATE, showing "02-Feb-02", but yielding the date value in the formula bar. Can you give me any hints? Ed "Bob Phillips" wrote in message ... Does it not work then. I ask because if the dates are just formatted differently, the underlying date values are the same. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I have a macro which AutoFilters a date column. The spreadsheet is now being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
I don't have time tonight to play with that (I'm at work), so I'll get back
to you tomorrow. Thank you for all your help. Ed "Bob Phillips" wrote in message ... Ed, Try changing the way the data is retrieved to Cell(r,c).Value=CDate("string") -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... No, Bob, it doesn't work. Not only that, but I've been trying desperately to do everything I can to make it work. I made a copy of the spreadsheet and changed the format of all these cells to DATE as yyyymmdd. That gave them the correct look, but they wouldn't filter with "greater than or equal to" - but they WILL filter on "equal to". So I copied the entire column into Word as a single-column table, the repasted back into Excel, just to have everything as pure text. Then I formatted the cells as NUMBER with no decimals. Same thing: filters on "equals" but not "greater than". The only thing I can think of is that I have changed the way I create this spreadsheet. The previous versions (in which the filter worked fine) were built from a database query table opened in Excel. The same column there is also formatted NUMBER, no decimals. But the numbers came out of the database as "20030202". This sheet, though, is built by searching a text doc for strings and inserting them into the cell as "Cell(r,c).Value="string". Doing this, the search finds the date field "02 Feb 2002" and writes it into the cell, where Excel automatically formats it as a DATE, showing "02-Feb-02", but yielding the date value in the formula bar. Can you give me any hints? Ed "Bob Phillips" wrote in message ... Does it not work then. I ask because if the dates are just formatted differently, the underlying date values are the same. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I have a macro which AutoFilters a date column. The spreadsheet is now being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
I'll watch out for it. Bedtime here.
Bob "Ed" wrote in message ... I don't have time tonight to play with that (I'm at work), so I'll get back to you tomorrow. Thank you for all your help. Ed "Bob Phillips" wrote in message ... Ed, Try changing the way the data is retrieved to Cell(r,c).Value=CDate("string") -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... No, Bob, it doesn't work. Not only that, but I've been trying desperately to do everything I can to make it work. I made a copy of the spreadsheet and changed the format of all these cells to DATE as yyyymmdd. That gave them the correct look, but they wouldn't filter with "greater than or equal to" - but they WILL filter on "equal to". So I copied the entire column into Word as a single-column table, the repasted back into Excel, just to have everything as pure text. Then I formatted the cells as NUMBER with no decimals. Same thing: filters on "equals" but not "greater than". The only thing I can think of is that I have changed the way I create this spreadsheet. The previous versions (in which the filter worked fine) were built from a database query table opened in Excel. The same column there is also formatted NUMBER, no decimals. But the numbers came out of the database as "20030202". This sheet, though, is built by searching a text doc for strings and inserting them into the cell as "Cell(r,c).Value="string". Doing this, the search finds the date field "02 Feb 2002" and writes it into the cell, where Excel automatically formats it as a DATE, showing "02-Feb-02", but yielding the date value in the formula bar. Can you give me any hints? Ed "Bob Phillips" wrote in message ... Does it not work then. I ask because if the dates are just formatted differently, the underlying date values are the same. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I have a macro which AutoFilters a date column. The spreadsheet is now being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format in string?
Bob: The cell value is lifted as is out of the report as a string. It is
inserted in the proper cell in the worksheet using a loop that works for all the cells, not just the DATE fields. As it is ("dd-mmm-yy"), it sorts fine; it just doesn't work with my established macros. So what I've done is go back to that column when I'm done and run down the cells with strLink = ActiveCell.Text If strLink < "" Then strLink = Format(strLink, "yyyymmdd") ActiveCell.NumberFormat = "General" ActiveCell.Value = CDec(strLink) End If I guess because it lifts it as a date, it accepts the formating to change it to the number. Then I reinsert it with the CDec (thank you for tipping me to these formatting codes!), and it goes in as a number that can be sorted. I don't know if there's a better way, but this is working for now. Thank you for all your help. Ed "Bob Phillips" wrote in message ... I'll watch out for it. Bedtime here. Bob "Ed" wrote in message ... I don't have time tonight to play with that (I'm at work), so I'll get back to you tomorrow. Thank you for all your help. Ed "Bob Phillips" wrote in message ... Ed, Try changing the way the data is retrieved to Cell(r,c).Value=CDate("string") -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... No, Bob, it doesn't work. Not only that, but I've been trying desperately to do everything I can to make it work. I made a copy of the spreadsheet and changed the format of all these cells to DATE as yyyymmdd. That gave them the correct look, but they wouldn't filter with "greater than or equal to" - but they WILL filter on "equal to". So I copied the entire column into Word as a single-column table, the repasted back into Excel, just to have everything as pure text. Then I formatted the cells as NUMBER with no decimals. Same thing: filters on "equals" but not "greater than". The only thing I can think of is that I have changed the way I create this spreadsheet. The previous versions (in which the filter worked fine) were built from a database query table opened in Excel. The same column there is also formatted NUMBER, no decimals. But the numbers came out of the database as "20030202". This sheet, though, is built by searching a text doc for strings and inserting them into the cell as "Cell(r,c).Value="string". Doing this, the search finds the date field "02 Feb 2002" and writes it into the cell, where Excel automatically formats it as a DATE, showing "02-Feb-02", but yielding the date value in the formula bar. Can you give me any hints? Ed "Bob Phillips" wrote in message ... Does it not work then. I ask because if the dates are just formatted differently, the underlying date values are the same. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I have a macro which AutoFilters a date column. The spreadsheet is now being created differently, and the dates are coming up in a different format. Currently, I have If MyTarget = "d" Then Selection.AutoFilter Field:=5, Criteria1:="=20020703", _ Operator:=xlAnd, Criteria2:="<=20030616" End If Is there any way to change these dates to "03-Jul-02" on the fly? Or do I need to go into the macro and redo the date for every If? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date string to date format | Excel Discussion (Misc queries) | |||
change a string of numbers in to number format | Excel Worksheet Functions | |||
Date format in string | Excel Discussion (Misc queries) | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming | |||
convert a string of into a date format | Excel Programming |