Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Hello!
I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
If I read your post correctly, each date column is formatted uniformly
within itself; that is, one column is formatted as actual dates and the other as the 5-digit number. Assume Column A is formatted as a date and Column B as the 5-digit number. Put this formula in row 2 (assuming that is the row your data starts in)... =IF(A2=DATE(2000+LEFT(B2),MID(B2,2,2),RIGHT(B2,2)) ,TRUE,FALSE) Rick "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
You can convert the numeric string to a date like this.
the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Hi,
In excel the number 10501 (depending on an options setting) represents 30 Sep 1928 and 1 may 2001 is represented by the number 37012 so to get a comparison to evaluate as true then you will have to add or subtract a fiddle factor. Why not simply use properly formatted dates? Mike "botany_girl" wrote: Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Perfect. Awesome. WAY beyond what I could have thought of--thank you so
much! I'm going to go try this out right now. -lisa "Rick Rothstein (MVP - VB)" wrote: If I read your post correctly, each date column is formatted uniformly within itself; that is, one column is formatted as actual dates and the other as the 5-digit number. Assume Column A is formatted as a date and Column B as the 5-digit number. Put this formula in row 2 (assuming that is the row your data starts in)... =IF(A2=DATE(2000+LEFT(B2),MID(B2,2,2),RIGHT(B2,2)) ,TRUE,FALSE) Rick "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
I would LOVE it if the dates were properly formatted. But the problem is
that I did not create the two datasets; they were created over years in two different programs (a basic text thing, which yielded the 5-digit date number, and Excel). Thanks, lisa "Mike H" wrote: Hi, In excel the number 10501 (depending on an options setting) represents 30 Sep 1928 and 1 may 2001 is represented by the number 37012 so to get a comparison to evaluate as true then you will have to add or subtract a fiddle factor. Why not simply use properly formatted dates? Mike "botany_girl" wrote: Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Thank you so much! Amazing.
"T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
.....the two datasets; they were created over years...
Over years? How many years? If more than 7, then the formulas Biff and I supplied you will have to be modified... we both assumed all dates happened in 2000 or later. Rick |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
I noted that in my reply and the OP seems to be satisfied!
-- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... .....the two datasets; they were created over years... Over years? How many years? If more than 7, then the formulas Biff and I supplied you will have to be modified... we both assumed all dates happened in 2000 or later. Rick |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Thank you so much! Amazing. "T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Ah, so you did... I didn't read the text of your message... I just looked at
the formula you included. Rick "T. Valko" wrote in message ... I noted that in my reply and the OP seems to be satisfied! -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... .....the two datasets; they were created over years... Over years? How many years? If more than 7, then the formulas Biff and I supplied you will have to be modified... we both assumed all dates happened in 2000 or later. Rick |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Yes, both formulas worked--thank you! The data is all from 2001-2007, from
each month of those years. So, there's lots of it, but all from years in which the "2000+" bit of your lovely formulas works perfectly. Thanks again, lisa "Rick Rothstein (MVP - VB)" wrote: Ah, so you did... I didn't read the text of your message... I just looked at the formula you included. Rick "T. Valko" wrote in message ... I noted that in my reply and the OP seems to be satisfied! -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... .....the two datasets; they were created over years... Over years? How many years? If more than 7, then the formulas Biff and I supplied you will have to be modified... we both assumed all dates happened in 2000 or later. Rick |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
thanks, this helped alot with our dates formatted like 20070703 for 7/1/2007
with a little editing to =DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to compare with dates in the MM/DD/YYYY format "T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Try this:
A1 = 20070703 Meaning: yyyy mm dd =--TEXT(A1,"0000\/00\/00") Format as Date -- Biff Microsoft Excel MVP "yesterdaytoday11" wrote in message ... thanks, this helped alot with our dates formatted like 20070703 for 7/1/2007 with a little editing to =DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to compare with dates in the MM/DD/YYYY format "T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
Excellent, this works too but what does the double negative sign do, reverse
the order? Where is this format found in Excel 'numbers tab and what does the back slash forward slash mean? "T. Valko" wrote: Try this: A1 = 20070703 Meaning: yyyy mm dd =--TEXT(A1,"0000\/00\/00") Format as Date -- Biff Microsoft Excel MVP "yesterdaytoday11" wrote in message ... thanks, this helped alot with our dates formatted like 20070703 for 7/1/2007 with a little editing to =DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to compare with dates in the MM/DD/YYYY format "T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
what does the double negative sign do
The TEXT() function returns a *text* string. The double unary "--" coerces the text number that is the date serial number to a numeric number and then formatting the cell displays it as a recognizeable date. Where is this format found in Excel 'numbers tab This format is not available in the menu FormatCellsNumber. You'd have to create it in the Custom category. what does the back slash forward slash mean? "0000 \ / 00 \ / 00" The forward slashes are the slashes used as date separators: 1/1/2009 The backslash is a "delimiter". With the number string: 20070703 The format means the first 4 digits are a group followed by a slash, the next 2 digits are a group followed by a slash and the last 2 digits are a group. This is "briefly" mentioned in Excel help under custom number formats. -- Biff Microsoft Excel MVP "yesterdaytoday11" wrote in message ... Excellent, this works too but what does the double negative sign do, reverse the order? Where is this format found in Excel 'numbers tab and what does the back slash forward slash mean? "T. Valko" wrote: Try this: A1 = 20070703 Meaning: yyyy mm dd =--TEXT(A1,"0000\/00\/00") Format as Date -- Biff Microsoft Excel MVP "yesterdaytoday11" wrote in message ... thanks, this helped alot with our dates formatted like 20070703 for 7/1/2007 with a little editing to =DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to compare with dates in the MM/DD/YYYY format "T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying date as a real number
I would also verify whether you want the DAYS360 function. I'll bet not.
Days360 will return 30 days between Feb 1st and March 1st of any year. If you want the proper number of days, just subtract, as in: =b1-a1 Regards, Fred. "yesterdaytoday11" wrote in message ... thanks, this helped alot with our dates formatted like 20070703 for 7/1/2007 with a little editing to =DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to compare with dates in the MM/DD/YYYY format "T. Valko" wrote: You can convert the numeric string to a date like this. the year is one digit in the source data Assuming the year is *always* in the 2000 decade A1 = 10501 B1 = 5/1/2001 =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2)) Returns 5/1/2001 So, to comapre the 2 dates: =DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1 -- Biff Microsoft Excel MVP "botany_girl" wrote in message ... Hello! I have a worksheet containing two datasets. Each set of data has associated dates--but they are entered as different numbers. One set has the date in format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one digit in the source data). The other set has the date entered as a date, i.e. 5/1/2001. I have the columns set up so that they display the same numbers, but the original data within the columns is formatted differently. I have to look at the data to make sure that the values in the two columns match. However, it's a huge dataset, so doing this manually is going to take a long time. I wanted to insert a logical argument [=IF(value1=value2,true,false] in a separate column to save time, but since the source data are in different formats, I get a FALSE every time. Any ideas on how I convert these beasts into identical formats so that I can use the logical argument strategy? The displayed numbers must not be changed to the odd Excel date-counting format, unfortunately--if that were the case it wouldn't be so difficult. Thanks for any suggestions, lisa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions | |||
real number | Excel Discussion (Misc queries) | |||
Convert real number to an h:mm:ss display | Excel Worksheet Functions | |||
real date for column | New Users to Excel | |||
how to include real time & date on a worksheet | Excel Discussion (Misc queries) |