Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a string of into a date format
Thanks it worked beautifully, but what if the fields are
blank, is there a way to modify this formula so that in stead on a #value being returned nothing will return? -----Original Message----- Hi Fred, "Fred" wrote in message ... I have a a bunch of dates and time in column M that are missing the /, I was wondering if it's possible to convert values of 08142003 12:30 into 08/14/2003 12:30. Any help would be great, thanks :) With one of those strings in cell M1, you could use a formula like this to get the date/time: =DATE(MID(M1,5,4),LEFT(M1,2),MID(M1,3,2))+TIME(MI D (M1,10,2),RIGHT(M1,2),0) Of course, this assumes your data is consistent. Regards, Jake Marx MS MVP - Excel . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a string of into a date format
Hi Fred,
=IF(M1="","", old formula ) which would provide an error if an empty or zero length cell is not the only problem. So you would notice it immediately and alter your formula to match the situation. If you just want to intercept all errors you could use... IF(ISERR(old formula),"", old formula) Suggest reading HELP, index (tab), IS Functions -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Fred" wrote in message ... Thanks it worked beautifully, but what if the fields are blank, is there a way to modify this formula so that in stead on a #value being returned nothing will return? -----Original Message----- Hi Fred, "Fred" wrote in message ... I have a a bunch of dates and time in column M that are missing the /, I was wondering if it's possible to convert values of 08142003 12:30 into 08/14/2003 12:30. Any help would be great, thanks :) With one of those strings in cell M1, you could use a formula like this to get the date/time: =DATE(MID(M1,5,4),LEFT(M1,2),MID(M1,3,2))+TIME(MI D (M1,10,2),RIGHT(M1,2),0) Of course, this assumes your data is consistent. Regards, Jake Marx MS MVP - Excel . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a string of into a date format
Fred,
You might want to consider using Len(M1) =If(Len(M1)=0,..... or =If(Len(M1)<???.... steve "David McRitchie" wrote in message ... Hi Fred, =IF(M1="","", old formula ) which would provide an error if an empty or zero length cell is not the only problem. So you would notice it immediately and alter your formula to match the situation. If you just want to intercept all errors you could use... IF(ISERR(old formula),"", old formula) Suggest reading HELP, index (tab), IS Functions -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Fred" wrote in message ... Thanks it worked beautifully, but what if the fields are blank, is there a way to modify this formula so that in stead on a #value being returned nothing will return? -----Original Message----- Hi Fred, "Fred" wrote in message ... I have a a bunch of dates and time in column M that are missing the /, I was wondering if it's possible to convert values of 08142003 12:30 into 08/14/2003 12:30. Any help would be great, thanks :) With one of those strings in cell M1, you could use a formula like this to get the date/time: =DATE(MID(M1,5,4),LEFT(M1,2),MID(M1,3,2))+TIME(MI D (M1,10,2),RIGHT(M1,2),0) Of course, this assumes your data is consistent. Regards, Jake Marx MS MVP - Excel . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date string to date format | Excel Discussion (Misc queries) | |||
Convert worksheet string name to date format in cell | Excel Worksheet Functions | |||
convert string to date | Excel Worksheet Functions | |||
Convert Date to STring | Excel Discussion (Misc queries) | |||
Can i convert numbers into string format? | Excel Discussion (Misc queries) |