Het bepalen van de Maand van Namen voor een datumbereik

Anita heeft een werkblad met meer dan 10.000 rijen. Elke rij vertegenwoordigt een individueel contract. Elk contract heeft een startdatum (kolom A) en een einddatum (kolom B). Ze heeft een manier om de lijst in de kolommen C, D, E, enz. De individuele maanden onder het contract. Bijvoorbeeld, als kolom A bevat 1 juli 2009 en kolom B bevat 30 september 2009 heeft ze nodig heeft kolom C tot juli 9 bevatten, kolom D tot 09 augustus en kolom E bevatten bevatten september 09. Wat formules worden gebruikt noodzaak om contract tegemoet lengtes die heel anders kunnen zijn.

Er zijn verschillende manieren om te gaan over een dergelijke taak. Het is mogelijk om een ​​aantal tussenliggende kolommen die dingen opgeven, zoals het starten van de maand en het aantal maanden van elk contract te gebruiken, maar het blijkt dat een dergelijke tussenliggende kolommen zijn niet nodig. Bijvoorbeeld, in de veronderstelling dat uw contracten beginnen in rij 2, kon je de volgende formule in cel C2 plaatsen:

= TEKST (A2, "mmm yy")

Dan, te beginnen in cel D2 kon je deze formule te plaatsen:

= ALS (DATUM (JAAR ($ A2), MAAND ($ A2) + COLUMNS ($ D2: D2),
DAG ($ A2))> $ B2, "", TEXT (DATUM (JAAR ($ A2), MAAND ($ A2)
+ COLUMNS ($ D2: D2), DAG ($ A2)), "mmm yy"))

Dit is een formule, en gebruikt de positie van de cellen met de formule samen met de contract begindatum te berekenen, in principe, de maand opzichte van die beginmaand. De formule kan worden gekopieerd naar rechts (kolom E, F, G, etc.) voor zover nodig alle maanden en jaren weergegeven.

Het enige nadeel aan de formule is dat als het contract einddatum eerder in de maand dan het contract begindatum is, dan is de laatste maand niet weergegeven. Dus, als het contract begint op 12 juni 2012 en eindigt op 5 februari 2013, toen de laatste maand (februari) zal niet worden weergegeven door de formule. Je zou, in plaats daarvan, gebruikt u de volgende formule in cel C2 en kopieer deze naar rechts zo ver als nodig is:

= ALS (DATUM (JAAR ($ A2), MAAND ($ A2) + COLUMN () - 2,0)
> DATUM (JAAR ($ A2), MAAND ($ B2) +1,0), "", TEXT (DATE
(JAAR ($ A2), MAAND ($ A2) + COLUMN () - 2,0), "mmm yy"))

Deze formule is ook afhankelijk van de kolommen waarin het zich bevindt, ze te gebruiken om een ​​offset vanaf de contractdatum start te berekenen. De formule werkt prima, ongeacht de verhouding tussen de overeenkomst begin- en einddatum.

Als u niet wilt vertrouwen op kolom positionering, er is een andere benadering die u kunt nemen. Plaats de volgende formule in cel C2:

= (A2)

Vervolgens, in cel D2 plaats de volgende formule:

= ALS ($ B2> C2, EOMONTH (C2,1), "")

Kopieer de formule in D2 naar rechts zoveel cellen als nodig, en formatteer al die cellen (met inbegrip van C2 en D2) als data die alleen de maand en het jaar weer te geven. De formule zal kijken naar B2 (het contract einddatum) om te zien of het groter is dan C2. Zo ja, dan is de formule geeft het volgnummer van de laatste dag van de volgende maand in serie. Zo niet, wordt een lege ("") geretourneerd.

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (5406) ​​is van toepassing op Microsoft Excel 97, 2000, 2002 en 2003. U kunt een versie van deze tip voor de ribbon-interface van Excel (Excel 2007 en later) vindt u hier: Bepalen Maand Namen voor een datumbereik.


© 2020 Quilcedacarvers.com | Contact us: webmaster# quilcedacarvers.com