Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Greatest difference between a consecutive range of dates
Hi,
I have several lists of dates with associated info, I am trying to work out if there is a simple function or way of calculating the largest interval between them. i.e sept 04 jan 05 feb 05 may 05 dec 06 Obviously the longest period is may 05 to dec 06 and is 19 months Any help would be greatly appreciated Hugo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Greatest difference between a consecutive range of dates
assuming tyou have true dates in A1:A10 and you want to find the maximum gap in whole months =MAX(DATEDIF(A1:A9,A2:A10,"m")) confirmed with CTRL+SHIFT+ENTER -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565151 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Greatest difference between a consecutive range of dates
Hi Hygsie,
use an auxiliar column to calc the difference between data and a conditional format to see the largest hth regards from Brazil Marcelo "Hugsie Bear" escreveu: Hi, I have several lists of dates with associated info, I am trying to work out if there is a simple function or way of calculating the largest interval between them. i.e sept 04 jan 05 feb 05 may 05 dec 06 Obviously the longest period is may 05 to dec 06 and is 19 months Any help would be greatly appreciated Hugo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Greatest difference between a consecutive range of dates
Why not do a simple subtraction to get a column of differences between adjacent dates and then use the MAX function to give you the largest of them? -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=565149 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Greatest difference between a consecutive range of dates
Thank you very much, a neat and simple solution
"daddylonglegs" wrote: assuming tyou have true dates in A1:A10 and you want to find the maximum gap in whole months =MAX(DATEDIF(A1:A9,A2:A10,"m")) confirmed with CTRL+SHIFT+ENTER -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565151 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tally a range of dates | Excel Worksheet Functions | |||
determining & returning least and greatest dates | Excel Worksheet Functions | |||
Return a value for a range of dates | Excel Worksheet Functions | |||
How do I select from within a range of dates? | Excel Discussion (Misc queries) | |||
I need the difference between two dates expressed as 4 years 3 mo. | Excel Discussion (Misc queries) |