Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
I am needing to get a function to do the following:
Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
One addition is that if I cannot concantenate the cell values, having them
populate in contiguous cells would work as well. "Paul" wrote: I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
Oh and I am using '07 version.
"Paul" wrote: I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
Look down a column(A) for any blank cell
Is there only one blank cell in column A? -- Biff Microsoft Excel MVP "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
I think that there will be many blank cells. But that is why step two must
look at a due date to see if it is overdue. "T. Valko" wrote: Look down a column(A) for any blank cell Is there only one blank cell in column A? -- Biff Microsoft Excel MVP "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
Ok, for every blank cell in column is there corresponding data in columns C
and D or might they also be blank? If there are many cells that meet these conditions this will take a very complicated formula. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I think that there will be many blank cells. But that is why step two must look at a due date to see if it is overdue. "T. Valko" wrote: Look down a column(A) for any blank cell Is there only one blank cell in column A? -- Biff Microsoft Excel MVP "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
No, C and D must contain data.
"T. Valko" wrote: Ok, for every blank cell in column is there corresponding data in columns C and D or might they also be blank? If there are many cells that meet these conditions this will take a very complicated formula. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I think that there will be many blank cells. But that is why step two must look at a due date to see if it is overdue. "T. Valko" wrote: Look down a column(A) for any blank cell Is there only one blank cell in column A? -- Biff Microsoft Excel MVP "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
and I am using '07 version.
Ok, this is fairly complicated so I did this the easiest and most efficient way I could think of. Assume the range of interest is A3:D25. Enter this formula in F2. This will return the count of how many records meet the criteria of column A being blank and the corresponding date in column B being greater than today's date. =COUNTIFS(A3:A25,"",B3:B25,""&TODAY()) Enter this array formula** in F3: =IF(ROWS(F$3:F3)<=F$2,SMALL(IF((A$3:A$25="")*(B$3: B$25TODAY()),ROW(A$3:A$25)),ROWS(F$3:F3)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Enter this formula G3: =IF(F3="","",INDEX(C:C,F3)&" "&TEXT(INDEX(D:D,F3),"m/d/yyyy")) Select both F3 and G3 then copy down until you get blanks. -- Biff Microsoft Excel MVP "Paul" wrote in message ... No, C and D must contain data. "T. Valko" wrote: Ok, for every blank cell in column is there corresponding data in columns C and D or might they also be blank? If there are many cells that meet these conditions this will take a very complicated formula. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I think that there will be many blank cells. But that is why step two must look at a due date to see if it is overdue. "T. Valko" wrote: Look down a column(A) for any blank cell Is there only one blank cell in column A? -- Biff Microsoft Excel MVP "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
I find your question to be somewhat unclear. You talk about Columns A, B, C
and D in general, but say you want your result in F3. If, as you said in an answer to Biff later on that you could have more than one blank in Column A to process, how did you want all that to fit in F3? If I can make a guess at what I think you meant... assuming for any give row, Column A's value is blank and Column B's value is a date past today (assumed to mean greater than today), then you want the concatenation of that row's Column C and D values placed into that row's Column F cell (with the date in Column D looking like a date). If this is correct, put this formula F3 and copy it down... =IF(AND(A3="",B3TODAY()),C3&TEXT(D3," m/d/yyyy"),"") You can control the display of the date from Column D by changing the date pattern (" m/d/yyyy") in the TEXT function to any date pattern you want (the TEXT function uses the same patterns as the Cell Formatting does). Note that I put a space in front of the 'm' in the date pattern... I did this so there would be a space between whatever is in Column C's cell and the date from Column D... if you don't want that space in the concatenated text, just remove it from the date pattern in the TEXT function. -- Rick (MVP - Excel) "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
Rick,
Thank you for your response. I am going to try what you have posted today. I am sorry that I was not really clear. Now that I see what you are asking here is a better explanation: Task | Due | Completed A | 1/1/2009 | 1/1/2009 B | 1/1/2009 | {BLANK} I would like to have the function on a different sheet that would pull Task B because there is no completed date and it was due before today. I would like it to pull the task information (Task name (B) and the Due date (1/1/2009) into a cell or series of cells. If possible, I would like it to pull any task that does not have a completed date that is due before today's date within the data set. I understand now, that this might need a new row for each entry, which would be great as well. Thank you again, -- Paul "Rick Rothstein" wrote: I find your question to be somewhat unclear. You talk about Columns A, B, C and D in general, but say you want your result in F3. If, as you said in an answer to Biff later on that you could have more than one blank in Column A to process, how did you want all that to fit in F3? If I can make a guess at what I think you meant... assuming for any give row, Column A's value is blank and Column B's value is a date past today (assumed to mean greater than today), then you want the concatenation of that row's Column C and D values placed into that row's Column F cell (with the date in Column D looking like a date). If this is correct, put this formula F3 and copy it down... =IF(AND(A3="",B3TODAY()),C3&TEXT(D3," m/d/yyyy"),"") You can control the display of the date from Column D by changing the date pattern (" m/d/yyyy") in the TEXT function to any date pattern you want (the TEXT function uses the same patterns as the Cell Formatting does). Note that I put a space in front of the 'm' in the date pattern... I did this so there would be a space between whatever is in Column C's cell and the date from Column D... if you don't want that space in the concatenated text, just remove it from the date pattern in the TEXT function. -- Rick (MVP - Excel) "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
I will try this today.
Thank you for the response. -- Paul "T. Valko" wrote: and I am using '07 version. Ok, this is fairly complicated so I did this the easiest and most efficient way I could think of. Assume the range of interest is A3:D25. Enter this formula in F2. This will return the count of how many records meet the criteria of column A being blank and the corresponding date in column B being greater than today's date. =COUNTIFS(A3:A25,"",B3:B25,""&TODAY()) Enter this array formula** in F3: =IF(ROWS(F$3:F3)<=F$2,SMALL(IF((A$3:A$25="")*(B$3: B$25TODAY()),ROW(A$3:A$25)),ROWS(F$3:F3)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Enter this formula G3: =IF(F3="","",INDEX(C:C,F3)&" "&TEXT(INDEX(D:D,F3),"m/d/yyyy")) Select both F3 and G3 then copy down until you get blanks. -- Biff Microsoft Excel MVP "Paul" wrote in message ... No, C and D must contain data. "T. Valko" wrote: Ok, for every blank cell in column is there corresponding data in columns C and D or might they also be blank? If there are many cells that meet these conditions this will take a very complicated formula. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I think that there will be many blank cells. But that is why step two must look at a due date to see if it is overdue. "T. Valko" wrote: Look down a column(A) for any blank cell Is there only one blank cell in column A? -- Biff Microsoft Excel MVP "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function based on blank cell and date past due
Rick,
That formula worked perfect for one cell in the data set. However, I was wondering if it were possible for it to scan through all of the records in the dataset. I though I might be able to accomplish this in either a named range or in an array, but I am a bit unsure. Thanks, -- Paul "Paul" wrote: Rick, Thank you for your response. I am going to try what you have posted today. I am sorry that I was not really clear. Now that I see what you are asking here is a better explanation: Task | Due | Completed A | 1/1/2009 | 1/1/2009 B | 1/1/2009 | {BLANK} I would like to have the function on a different sheet that would pull Task B because there is no completed date and it was due before today. I would like it to pull the task information (Task name (B) and the Due date (1/1/2009) into a cell or series of cells. If possible, I would like it to pull any task that does not have a completed date that is due before today's date within the data set. I understand now, that this might need a new row for each entry, which would be great as well. Thank you again, -- Paul "Rick Rothstein" wrote: I find your question to be somewhat unclear. You talk about Columns A, B, C and D in general, but say you want your result in F3. If, as you said in an answer to Biff later on that you could have more than one blank in Column A to process, how did you want all that to fit in F3? If I can make a guess at what I think you meant... assuming for any give row, Column A's value is blank and Column B's value is a date past today (assumed to mean greater than today), then you want the concatenation of that row's Column C and D values placed into that row's Column F cell (with the date in Column D looking like a date). If this is correct, put this formula F3 and copy it down... =IF(AND(A3="",B3TODAY()),C3&TEXT(D3," m/d/yyyy"),"") You can control the display of the date from Column D by changing the date pattern (" m/d/yyyy") in the TEXT function to any date pattern you want (the TEXT function uses the same patterns as the Cell Formatting does). Note that I put a space in front of the 'm' in the date pattern... I did this so there would be a space between whatever is in Column C's cell and the date from Column D... if you don't want that space in the concatenated text, just remove it from the date pattern in the TEXT function. -- Rick (MVP - Excel) "Paul" wrote in message ... I am needing to get a function to do the following: Look down a column(A) for any blank cell, then for that blank cell look in the cell to the right in column B and see if the date is past today. If this is true, I want the values of the corresponding cells in column C and D concantenated and inserted into cell F3. Another major issue is that the value in the cell for column D is a date and when it is inserted it comes as the number value instead of the typical date. Thank you for any help, -- Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
past cell by date | Excel Worksheet Functions | |||
past date cell colour change | Excel Worksheet Functions | |||
Function to give value of a BLANK cell based on another | Excel Worksheet Functions | |||
flag date within a cell after 15 days have past? | Excel Worksheet Functions | |||
Date/If function for past dues | Excel Worksheet Functions |