Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
Wonder if anyone can help.
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? Help MIJC |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Mon, 5 Dec 2005 01:58:02 -0800, "MijC"
wrote: Wonder if anyone can help. 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? Help MIJC What is the function in Column A? (Excel has many date and time functions). What are the inputs to the Column A function and what do you obtain for outputs or display? When you say the dd/mm is switched to mm/dd, is that in the downloaded data or is that after you have performed some Excel operation on the downloaded data? Are you perhaps not using the word "function" as it is used in Excel, but rather merely mean that you are downloading date/time strings from the "web" into column A? If that is the case, it is likely that all of the strings in Column A are, in fact, text strings. This can be easily determined by using the formula =ISTEXT(A1) in some unused column and copying it down as far as necessary. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
Ron,
Please find below the data as shown in the download: COLUMN A COLUMN B COLUMN C 11/12/2005 23:20 11/12/DN D 11/13/2005 00:08:03 11/12/DN D In Row 1 the date is not text In row 2 the date is tex in column B I use the follow formula =CONCATENATE(IF(HOUR(A1062)=7,DAY(A1062),DAY(A106 2)-1),"/",MONTH(A1062),"/",F1062,IF(OR(HOUR(A1062)<7,HOUR(A1062)=19),"N"," D")) Column c show the shift that is working at that time. Shift time from 7am to 7pm and 7pm to 7 am denoted by D (days) or N (nights) Due to row 2 being text the above formula cannot be use. I then use left() mid() right() functions. this gives me issues with the day or night function. require further columns for this. Is there some way of determining which formula to use fom analysing using the istext function ie. use blak colum as you said for istext(a1) if say b1 = true then use left(),mid() etc.. IF b1 = false then fourmula above. I believe this will still give me an issue with the D or N function thanks for the help "Ron Rosenfeld" wrote: On Mon, 5 Dec 2005 01:58:02 -0800, "MijC" wrote: Wonder if anyone can help. 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? Help MIJC What is the function in Column A? (Excel has many date and time functions). What are the inputs to the Column A function and what do you obtain for outputs or display? When you say the dd/mm is switched to mm/dd, is that in the downloaded data or is that after you have performed some Excel operation on the downloaded data? Are you perhaps not using the word "function" as it is used in Excel, but rather merely mean that you are downloading date/time strings from the "web" into column A? If that is the case, it is likely that all of the strings in Column A are, in fact, text strings. This can be easily determined by using the formula =ISTEXT(A1) in some unused column and copying it down as far as necessary. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Tue, 6 Dec 2005 00:34:33 -0800, "MijC"
wrote: Ron, Please find below the data as shown in the download: COLUMN A COLUMN B COLUMN C 11/12/2005 23:20 11/12/DN D 11/13/2005 00:08:03 11/12/DN D In Row 1 the date is not text In row 2 the date is tex in column B I use the follow formula =CONCATENATE(IF(HOUR(A1062)=7,DAY(A1062),DAY(A10 62)-1),"/",MONTH(A1062),"/",F1062,IF(OR(HOUR(A1062)<7,HOUR(A1062)=19),"N"," D")) Column c show the shift that is working at that time. Shift time from 7am to 7pm and 7pm to 7 am denoted by D (days) or N (nights) Due to row 2 being text the above formula cannot be use. I then use left() mid() right() functions. this gives me issues with the day or night function. require further columns for this. Is there some way of determining which formula to use fom analysing using the istext function ie. use blak colum as you said for istext(a1) if say b1 = true then use left(),mid() etc.. IF b1 = false then fourmula above. I believe this will still give me an issue with the D or N function thanks for the help As I wrote in my previous post, you can always figure out if a date string is TEXT or not by using the =ISTEXT(cell_ref) function. What happened when you used that function on the cells in column A? But I would use a different approach for consistency. I would ensure that the data in Column A is processed as a true date, rather than trying to pick it apart and figure out if it is text or not. You can then use date functions which will simplify things quite a bit. One of the potential problems with your formula is if the DAY happens to be the first day of the month, and HOUR < 7 , then wouldn't you also need to change your month to the previous month? And you couldn't just subtract '1' but you'd also have to take into account the month before '1' is '12'. Much simpler to use a single formula that uses date functions, such as: =TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)-1+(HOUR(A1)=7)), "dd/mm/\"&F1& IF(OR(HOUR(A1)<7,HOUR(A1)=19),"\N","\D")) So let's figure out why some of your data is text and why some of it is real dates. 1. If you do =ISTEXT(cell_ref) where cell_ref are the cells in colA that you think are and are not text, do you get the expected results or are they all TRUE? 2. How does the data get into column A? Is it manually entered?, the result of a formula?, copied from the web or an HTML document?, etc??? 3. Is the Day/Month/Year order the same as in your Windows/Control Panel/Language and Regional Settings configuration? --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
Ron,
1. The data shows true and false. Both results are shown. Therefore both data and text in colA 2. Data is downloaded from our IT database. (I have spoke to them several times on this issues yet no resolve) It would be recorded at the time of the transtaction there is a date/text stamp used. How it get thre I am not 100% sure. Will try to find out. (I know this is the issue, but I need a work around until this is fixed, if ever) 3. Configuration setting is correct. the day/month/year difference comes form item 2. Not sure way again Hope this is of some use "Ron Rosenfeld" wrote: On Tue, 6 Dec 2005 00:34:33 -0800, "MijC" wrote: Ron, Please find below the data as shown in the download: COLUMN A COLUMN B COLUMN C 11/12/2005 23:20 11/12/DN D 11/13/2005 00:08:03 11/12/DN D In Row 1 the date is not text In row 2 the date is tex in column B I use the follow formula =CONCATENATE(IF(HOUR(A1062)=7,DAY(A1062),DAY(A10 62)-1),"/",MONTH(A1062),"/",F1062,IF(OR(HOUR(A1062)<7,HOUR(A1062)=19),"N"," D")) Column c show the shift that is working at that time. Shift time from 7am to 7pm and 7pm to 7 am denoted by D (days) or N (nights) Due to row 2 being text the above formula cannot be use. I then use left() mid() right() functions. this gives me issues with the day or night function. require further columns for this. Is there some way of determining which formula to use fom analysing using the istext function ie. use blak colum as you said for istext(a1) if say b1 = true then use left(),mid() etc.. IF b1 = false then fourmula above. I believe this will still give me an issue with the D or N function thanks for the help As I wrote in my previous post, you can always figure out if a date string is TEXT or not by using the =ISTEXT(cell_ref) function. What happened when you used that function on the cells in column A? But I would use a different approach for consistency. I would ensure that the data in Column A is processed as a true date, rather than trying to pick it apart and figure out if it is text or not. You can then use date functions which will simplify things quite a bit. One of the potential problems with your formula is if the DAY happens to be the first day of the month, and HOUR < 7 , then wouldn't you also need to change your month to the previous month? And you couldn't just subtract '1' but you'd also have to take into account the month before '1' is '12'. Much simpler to use a single formula that uses date functions, such as: =TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)-1+(HOUR(A1)=7)), "dd/mm/\"&F1& IF(OR(HOUR(A1)<7,HOUR(A1)=19),"\N","\D")) So let's figure out why some of your data is text and why some of it is real dates. 1. If you do =ISTEXT(cell_ref) where cell_ref are the cells in colA that you think are and are not text, do you get the expected results or are they all TRUE? 2. How does the data get into column A? Is it manually entered?, the result of a formula?, copied from the web or an HTML document?, etc??? 3. Is the Day/Month/Year order the same as in your Windows/Control Panel/Language and Regional Settings configuration? --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Tue, 6 Dec 2005 05:52:02 -0800, "MijC"
wrote: 2. Data is downloaded from our IT database. (I have spoke to them several times on this issues yet no resolve) It would be recorded at the time of the transtaction there is a date/text stamp used. How it get thre I am not 100% sure. Will try to find out. (I know this is the issue, but I need a work around until this is fixed, if ever) If that is the case, I think the work around should be to convert the TEXT strings to real dates. Let's try a few things. 1. With one of the strings were ISTEXT(cell_ref) = TRUE 1. Copy/Paste into your response the contents of the cell. 2. What is the result of the formula =LEN(cell_ref) ?? (This should tell us if there are any non-printing characters). 2. What happens if you prepend a double unary (--) to the TEXT strings? (i.e. =--11/13/2005 00:08:03 That might force the string to become a true date/time (number) and, if it works, we can use a much simpler procedure to apply it to everything. If that does not work, then the results of procedure 1 above will become important. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
Ron,
1. For true shows the following info : 04/01/1900 00:00 actual date in COL A 11/13/2005 00:08:03 2. this gives me an error message. Regards Jim "Ron Rosenfeld" wrote: On Tue, 6 Dec 2005 05:52:02 -0800, "MijC" wrote: 2. Data is downloaded from our IT database. (I have spoke to them several times on this issues yet no resolve) It would be recorded at the time of the transtaction there is a date/text stamp used. How it get thre I am not 100% sure. Will try to find out. (I know this is the issue, but I need a work around until this is fixed, if ever) If that is the case, I think the work around should be to convert the TEXT strings to real dates. Let's try a few things. 1. With one of the strings were ISTEXT(cell_ref) = TRUE 1. Copy/Paste into your response the contents of the cell. 2. What is the result of the formula =LEN(cell_ref) ?? (This should tell us if there are any non-printing characters). 2. What happens if you prepend a double unary (--) to the TEXT strings? (i.e. =--11/13/2005 00:08:03 That might force the string to become a true date/time (number) and, if it works, we can use a much simpler procedure to apply it to everything. If that does not work, then the results of procedure 1 above will become important. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Tue, 6 Dec 2005 08:00:03 -0800, "MijC"
wrote: 1. For true shows the following info : 04/01/1900 00:00 actual date in COL A 11/13/2005 00:08:03 What about =LEN(An) ?? --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Tue, 06 Dec 2005 13:54:57 -0500, Ron Rosenfeld
wrote: On Tue, 6 Dec 2005 08:00:03 -0800, "MijC" wrote: 1. For true shows the following info : 04/01/1900 00:00 actual date in COL A 11/13/2005 00:08:03 What about =LEN(An) ?? --ron Never mind. I see that you mean that LEN(An) shows 04/01/1900 00:00. I will get back to you. --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Tue, 06 Dec 2005 13:54:57 -0500, Ron Rosenfeld
wrote: On Tue, 6 Dec 2005 08:00:03 -0800, "MijC" wrote: 1. For true shows the following info : 04/01/1900 00:00 actual date in COL A 11/13/2005 00:08:03 What about =LEN(An) ?? --ron LEN(A1) where A1 contains 11/13/2005 00:08:03 being equal to 04/01/1900 doesn't make sense to me as that translates to 92; and that seems to be to many non-printing characters for something that displays as a date/time string. Try this formula in a column adjacent to your date time strings. The formula needs to be **array-entered** which means after typing/pasting it in, you need to hold down <ctrl<shift while hitting <enter. If you did it properly, in the function bar you will see that Excel has placed braces {...} around the formula. The formula should translate the date/time text strings to a true date. --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function/Text issue
On Tue, 06 Dec 2005 15:38:29 -0500, Ron Rosenfeld
wrote: On Tue, 06 Dec 2005 13:54:57 -0500, Ron Rosenfeld wrote: On Tue, 6 Dec 2005 08:00:03 -0800, "MijC" wrote: 1. For true shows the following info : 04/01/1900 00:00 actual date in COL A 11/13/2005 00:08:03 What about =LEN(An) ?? --ron LEN(A1) where A1 contains 11/13/2005 00:08:03 being equal to 04/01/1900 doesn't make sense to me as that translates to 92; and that seems to be to many non-printing characters for something that displays as a date/time string. Try this formula in a column adjacent to your date time strings. The formula needs to be **array-entered** which means after typing/pasting it in, you need to hold down <ctrl<shift while hitting <enter. If you did it properly, in the function bar you will see that Excel has placed braces {...} around the formula. The formula should translate the date/time text strings to a true date. --ron Here is the formula: =IF(ISERR(-A1),--MID(A1,SEARCH("??/",A1),LEN(A1) +2-SEARCH("??/",A1)-MATCH(TRUE,ISNUMBER( -MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"& LEN(A1))),1)),0)),A1) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date format issue | Excel Worksheet Functions | |||
Another Date issue. | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Date conversion issue "^" symbol | Excel Discussion (Misc queries) | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) |