Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate next number using dates?
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every instance of the same date in a column. For instance, if there are two instances of the date 5/9/2004 in the same column, then I want to display a value similar to this in the same row of a different column: 38116.01 for one instance, and with the next instance having this value: 38116.02. This value should increase incrementally by .01 every time a new instance of the same date is added to the column. I hope this makes sense and that it's possible with a formula, not a macro. Thanks in advance for your help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate next number using dates?
If there are more than 100 instances then the date will advance after
38116.99 I'm guessing that that is not a possibility? =A1+COUNTIF(A$1:A1,A1)/100 Copy down as needed. Format as GENERAL to get the serial number. Biff "cyndiwise notsowise" wrote in message ... Here's what I'm trying to do: Using Excel's date serial numbers, create a unique value for every instance of the same date in a column. For instance, if there are two instances of the date 5/9/2004 in the same column, then I want to display a value similar to this in the same row of a different column: 38116.01 for one instance, and with the next instance having this value: 38116.02. This value should increase incrementally by .01 every time a new instance of the same date is added to the column. I hope this makes sense and that it's possible with a formula, not a macro. Thanks in advance for your help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate next number using dates?
Thanks for your quick reply, T. Valko. You are correct, there should never be
more than 99 instances of the same date (far less, I'm sure!) However, I tried the formula and it numbers every instance of the date the same. I need the numbers to be unique for every instance: 38116.01 38116.02 38116.03 and so on... Do you think this is possible? cyndiwise ---------------------------------------------------------------------------------------- "T. Valko" wrote: If there are more than 100 instances then the date will advance after 38116.99 I'm guessing that that is not a possibility? =A1+COUNTIF(A$1:A1,A1)/100 Copy down as needed. Format as GENERAL to get the serial number. Biff "cyndiwise notsowise" wrote in message ... Here's what I'm trying to do: Using Excel's date serial numbers, create a unique value for every instance of the same date in a column. For instance, if there are two instances of the date 5/9/2004 in the same column, then I want to display a value similar to this in the same row of a different column: 38116.01 for one instance, and with the next instance having this value: 38116.02. This value should increase incrementally by .01 every time a new instance of the same date is added to the column. I hope this makes sense and that it's possible with a formula, not a macro. Thanks in advance for your help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate next number using dates?
The formula I suggested does exactly what you want. Post the *exact* formula
you tried. Biff "cyndiwise notsowise" wrote in message ... Thanks for your quick reply, T. Valko. You are correct, there should never be more than 99 instances of the same date (far less, I'm sure!) However, I tried the formula and it numbers every instance of the date the same. I need the numbers to be unique for every instance: 38116.01 38116.02 38116.03 and so on... Do you think this is possible? cyndiwise ---------------------------------------------------------------------------------------- "T. Valko" wrote: If there are more than 100 instances then the date will advance after 38116.99 I'm guessing that that is not a possibility? =A1+COUNTIF(A$1:A1,A1)/100 Copy down as needed. Format as GENERAL to get the serial number. Biff "cyndiwise notsowise" wrote in message ... Here's what I'm trying to do: Using Excel's date serial numbers, create a unique value for every instance of the same date in a column. For instance, if there are two instances of the date 5/9/2004 in the same column, then I want to display a value similar to this in the same row of a different column: 38116.01 for one instance, and with the next instance having this value: 38116.02. This value should increase incrementally by .01 every time a new instance of the same date is added to the column. I hope this makes sense and that it's possible with a formula, not a macro. Thanks in advance for your help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate next number using dates?
Oops! I "translated" your formula incorrectly for my worksheet. Column A
contains the formula, Column B contains the date, Row 1 is a Header Row. Now the values are listing sequentially with this formula: =B2+COUNTIF(B$2:B2,B2)/100 However, now there's an error message displaying for some of the cells containing the formula. The error message says: "the formula in this cell refers to a range that has additional numbers adjacent to it". Did I still do something wrong in translating the formula to fit my worksheet? I have Columns A thru Q, some of which have numbers and others that contain text, and some contain other formulas. Thanks so much for your help. cyndiwise --------------------------------------------------------------------------------------- "T. Valko" wrote: The formula I suggested does exactly what you want. Post the *exact* formula you tried. Biff "cyndiwise notsowise" wrote in message ... Thanks for your quick reply, T. Valko. You are correct, there should never be more than 99 instances of the same date (far less, I'm sure!) However, I tried the formula and it numbers every instance of the date the same. I need the numbers to be unique for every instance: 38116.01 38116.02 38116.03 and so on... Do you think this is possible? cyndiwise ---------------------------------------------------------------------------------------- "T. Valko" wrote: If there are more than 100 instances then the date will advance after 38116.99 I'm guessing that that is not a possibility? =A1+COUNTIF(A$1:A1,A1)/100 Copy down as needed. Format as GENERAL to get the serial number. Biff "cyndiwise notsowise" wrote in message ... Here's what I'm trying to do: Using Excel's date serial numbers, create a unique value for every instance of the same date in a column. For instance, if there are two instances of the date 5/9/2004 in the same column, then I want to display a value similar to this in the same row of a different column: 38116.01 for one instance, and with the next instance having this value: 38116.02. This value should increase incrementally by .01 every time a new instance of the same date is added to the column. I hope this makes sense and that it's possible with a formula, not a macro. Thanks in advance for your help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate next number using dates?
now there's an error message displaying for some of the cells
containing the formula. Is the result of the formula correct? I suspect that's just Excel trying to be "helpful". Those are error checking messages that Excel displays. My version (Excel 2002) doesn't have that particular message. Personally, I hate them and find them to be an intrusive PITA and have turned them off. All those colored triangles! Argh! The setting for those messages can be found at ToolsOptionsError CheckingRules. If you want to turn them off (or some of them) just uncheck them under Rules. Biff "cyndiwise notsowise" wrote in message ... Oops! I "translated" your formula incorrectly for my worksheet. Column A contains the formula, Column B contains the date, Row 1 is a Header Row. Now the values are listing sequentially with this formula: =B2+COUNTIF(B$2:B2,B2)/100 However, now there's an error message displaying for some of the cells containing the formula. The error message says: "the formula in this cell refers to a range that has additional numbers adjacent to it". Did I still do something wrong in translating the formula to fit my worksheet? I have Columns A thru Q, some of which have numbers and others that contain text, and some contain other formulas. Thanks so much for your help. cyndiwise --------------------------------------------------------------------------------------- "T. Valko" wrote: The formula I suggested does exactly what you want. Post the *exact* formula you tried. Biff "cyndiwise notsowise" wrote in message ... Thanks for your quick reply, T. Valko. You are correct, there should never be more than 99 instances of the same date (far less, I'm sure!) However, I tried the formula and it numbers every instance of the date the same. I need the numbers to be unique for every instance: 38116.01 38116.02 38116.03 and so on... Do you think this is possible? cyndiwise ---------------------------------------------------------------------------------------- "T. Valko" wrote: If there are more than 100 instances then the date will advance after 38116.99 I'm guessing that that is not a possibility? =A1+COUNTIF(A$1:A1,A1)/100 Copy down as needed. Format as GENERAL to get the serial number. Biff "cyndiwise notsowise" wrote in message ... Here's what I'm trying to do: Using Excel's date serial numbers, create a unique value for every instance of the same date in a column. For instance, if there are two instances of the date 5/9/2004 in the same column, then I want to display a value similar to this in the same row of a different column: 38116.01 for one instance, and with the next instance having this value: 38116.02. This value should increase incrementally by .01 every time a new instance of the same date is added to the column. I hope this makes sense and that it's possible with a formula, not a macro. Thanks in advance for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate number of days btw dates | Excel Worksheet Functions | |||
Calculate number of months between 2 dates | New Users to Excel | |||
Calculate number of months between 2 dates | New Users to Excel | |||
HOW TO CALCULATE THE NUMBER OF YEARS BETWEEN TWO DATES | Excel Worksheet Functions | |||
HOW TO CALCULATE NUMBER OF WEEKS BETWEEN TWO GIVEN DATES(MAY BE . | Excel Worksheet Functions |