Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Suggestion required
I have a table with the following information
Pay Date End Period 07-09-2006 0809 05-10-2006 0909 02-11-2006 1009 30-11-2006 1109 21-12-2006 1209 What I want to be able to do is enter a date for example 15/11/2006, and it tell me what the end period would be. Therefore it would look for the first higher paydate, in this case 30/11/2006 so therefore return a value of 1109. Many Thanks in advance for your assistance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Suggestion required
If you have the toolpak installed
=EOMONTH(D12,0) if not =DATE(YEAR(D13),MONTH(D13)+1,0) -- Don Guillett SalesAid Software "Pendelfin" wrote in message ... I have a table with the following information Pay Date End Period 07-09-2006 0809 05-10-2006 0909 02-11-2006 1009 30-11-2006 1109 21-12-2006 1209 What I want to be able to do is enter a date for example 15/11/2006, and it tell me what the end period would be. Therefore it would look for the first higher paydate, in this case 30/11/2006 so therefore return a value of 1109. Many Thanks in advance for your assistance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Suggestion required
If your pay date table is at A2:A6, and your end period codes at B2:B6, with
your input date at A9, try =INDEX(B2:B6,1+MATCH(A9,A2:A6,1)) -- David Biddulph "Pendelfin" wrote in message ... I have a table with the following information Pay Date End Period 07-09-2006 0809 05-10-2006 0909 02-11-2006 1009 30-11-2006 1109 21-12-2006 1209 What I want to be able to do is enter a date for example 15/11/2006, and it tell me what the end period would be. Therefore it would look for the first higher paydate, in this case 30/11/2006 so therefore return a value of 1109. Many Thanks in advance for your assistance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Suggestion required
David
Thanks this works great unless you enter a date that is in the table, then it returns the following period, I have resolved this by adding an If iserror vlookup =IF(ISERROR(VLOOKUP(A9,A2:B6,2,FALSE)),INDEX(B2:B6 ,1+MATCH(A9,A2:A6,1)),VLOOKUP(A9,A2:B6,2,FALSE)). However if I enter a date before the first date in the table i receive an N/A? PLease can you let m e know how this could be resolved? "David Biddulph" wrote: If your pay date table is at A2:A6, and your end period codes at B2:B6, with your input date at A9, try =INDEX(B2:B6,1+MATCH(A9,A2:A6,1)) -- David Biddulph "Pendelfin" wrote in message ... I have a table with the following information Pay Date End Period 07-09-2006 0809 05-10-2006 0909 02-11-2006 1009 30-11-2006 1109 21-12-2006 1209 What I want to be able to do is enter a date for example 15/11/2006, and it tell me what the end period would be. Therefore it would look for the first higher paydate, in this case 30/11/2006 so therefore return a value of 1109. Many Thanks in advance for your assistance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Relative vs Absolute referencing of Workbooks | Excel Worksheet Functions | |||
Relative vs Absolute referencing of Workbooks | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Rate of return required formula | Excel Worksheet Functions |