Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return first date populated
I have a spreadsheet with 24 months across the columns. The rows contain
customer names. The cells contain data beginning with the date that amounts were transferred into the department. Is there a function that will allow me to look across the columns and return the first date that is populated? I tried to use a conditional if statement, but the number of periods are greater than the number of conditions allowed? -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return first date populated
How is the data laid out in your workbook?, are dates in their seperate cells? Pleasehelp via OfficeKB.com;324122 Wrote: I have a spreadsheet with 24 months across the columns. The rows contain customer names. The cells contain data beginning with the date that amounts were transferred into the department. Is there a function that will allow me to look across the columns and return the first date that is populated? I tried to use a conditional if statement, but the number of periods are greater than the number of conditions allowed? -- Message posted via 'MS Office forums. More than 400,000 of Word, Excel, Outlook, PowerPoint, Publisher, InfoPath discussions.' (http://www.officekb.com) -- The Code Cage Team Regards, The Code Cage Team 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90571 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return first date populated
If the dates (if any are entered) just happen to be in ascending order:
=IF(COUNT(A1:J1),MIN(A1:J1),"") Format as Date If the dates are random try this array formula** : =IF(COUNT(A1:J1),INDEX(A1:J1,MATCH(TRUE,ISNUMBER(A 1:J1),0)),"") Format as Date ** 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. -- Biff Microsoft Excel MVP "Pleasehelp via OfficeKB.com" <u27204@uwe wrote in message news:953df1a279d74@uwe... I have a spreadsheet with 24 months across the columns. The rows contain customer names. The cells contain data beginning with the date that amounts were transferred into the department. Is there a function that will allow me to look across the columns and return the first date that is populated? I tried to use a conditional if statement, but the number of periods are greater than the number of conditions allowed? -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return first date populated
Dates are arranged in ascending order in Row 1. The data contained in the
cells is the $ value of the receivable as of that date. It is column H I am trying to populate. A B C D E F G H Acct # Name 11/30/2008 12/31/2008 1/31/2009 2/28/2009 3/31/2009 1stDate 1234567 ABC Co $10,001. 27 $10,001.27 2/28/2009 2345678 BCD Co $47, 161.57 $47,161.57 2/28/2009 3456789 CDE Co $10,000.00 12/31/2008 4567890 DEF Co $5,500.00 ??? Thanks. The Code Cage Team wrote: How is the data laid out in your workbook?, are dates in their seperate cells? Pleasehelp via OfficeKB.com;324122 Wrote: I have a spreadsheet with 24 months across the columns. The rows contain [quoted text clipped - 11 lines] Outlook, PowerPoint, Publisher, InfoPath discussions.' (http://www.officekb.com) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return first date populated
I see now, the dates are the column headers.
The array formula** I suggested will do what you want. Just change the references to suit. =IF(COUNT(B2:G2),INDEX(B$1G$1,MATCH(TRUE,ISNUMBER( B2:G2),0)),"") Format as Date ** 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. -- Biff Microsoft Excel MVP "Pleasehelp via OfficeKB.com" <u27204@uwe wrote in message news:953ee28251ebc@uwe... Dates are arranged in ascending order in Row 1. The data contained in the cells is the $ value of the receivable as of that date. It is column H I am trying to populate. A B C D E F G H Acct # Name 11/30/2008 12/31/2008 1/31/2009 2/28/2009 3/31/2009 1stDate 1234567 ABC Co $10,001. 27 $10,001.27 2/28/2009 2345678 BCD Co $47, 161.57 $47,161.57 2/28/2009 3456789 CDE Co $10,000.00 12/31/2008 4567890 DEF Co $5,500.00 ??? Thanks. The Code Cage Team wrote: How is the data laid out in your workbook?, are dates in their seperate cells? Pleasehelp via OfficeKB.com;324122 Wrote: I have a spreadsheet with 24 months across the columns. The rows contain [quoted text clipped - 11 lines] Outlook, PowerPoint, Publisher, InfoPath discussions.' (http://www.officekb.com) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I merge date from two populated columns into one column? | Setting up and Configuration of Excel | |||
Return nothing if cell is populated | Excel Discussion (Misc queries) | |||
auto enter date when another cell populated? | New Users to Excel | |||
Payment cell populated based on date formula | Excel Discussion (Misc queries) | |||
Auto insert Date & Time when another cell is populated | Excel Discussion (Misc queries) |