In deze video bekijken we hoe u vervaldatums kunt berekenen en markeren.
Stel dat uw bedrijf een soort lidmaatschapsprogramma is gestart en dat uw baas u zojuist een set gegevens heeft gestuurd. Ze heeft je een lijst gegeven van 1000 mensen die het afgelopen jaar een lidmaatschap hebben verlengd en ze is op zoek naar verschillende dingen.
Ten eerste wil ze dat je een vervaldatum berekent die één jaar in de toekomst ligt, op de laatste dag van dezelfde maand dat het lidmaatschap is verlengd.
Ten tweede wil ze zien hoeveel dagen er nog resteren.
Ten derde wil ze de status 'Verlopen' zien voor al verlopen lidmaatschappen en 'Verloopt binnenkort' voor alle lidmaatschappen die in de komende 30 dagen aflopen.
Ten slotte zei ze dat het leuk zou zijn om verlopen leden in het roze te zien, en degenen die al snel vervallen in geel.
Ook - mag ze het hebben voor haar lunchbijeenkomst om 12.00 uur?
Umm, natuurlijk.
Laten we eerst deze gegevens converteren naar een goede Excel-tabel. Dit maakt het invoeren van formules veel gemakkelijker, aangezien Excel ze automatisch zal kopiëren, terwijl we bezig zijn.
Laten we nu de vervaldatums berekenen. Deze zouden een jaar later aan het einde van dezelfde maand moeten zijn, maar laten we eerst een simpele hack gebruiken om in het honk te komen. Zoals je in eerdere video's hebt gezien, zijn datums slechts serienummers, dus we kunnen gewoon een formule invoeren die 365 dagen toevoegt aan de vernieuwde datum.
Dit is een goed begin. We kunnen de oplossing afmaken en later terugkomen om dit op te lossen.
Wanneer u een complexer probleem in Excel oplost, is het een goed idee om de algemene aanpak te valideren en aan het einde terug te komen op de details. Je wilt niet meteen aan een klein ding vastzitten, vooral als de aanpak kan veranderen.
Nu we een vervaldatum hebben, kunnen we nu de resterende dagen berekenen. Dit moet in de toekomst automatisch worden bijgewerkt, dus we zullen de functie TODAY gebruiken, die altijd de datum van vandaag retourneert.
De formule is gewoon E5 min VANDAAG (). Wanneer ik op Return druk, krijgen we de resterende dagen vóór de vervaldatum. Negatieve cijfers geven aan dat een lidmaatschap al is verlopen.
Voor status gebruiken we een eenvoudige geneste IF-formule. Als het aantal resterende dagen minder is dan nul, is het lidmaatschap verlopen. Als dat niet het geval is, als de resterende dagen minder dan 30 zijn, zou de status "Verloopt binnenkort" moeten zijn. Anders is de status niets.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Vervolgens moeten we voorwaardelijke opmaakregels maken die deze waarden benadrukken.
Selecteer eerst de gegevens en plaats de actieve cel in de rechterbovenhoek. Maak vervolgens een formuleregel die de actieve cel test op de waarde "Verlopen". De kolom moet vergrendeld zijn.
Herhaal nu hetzelfde proces voor lidmaatschappen die binnenkort verlopen.
Dit ziet er goed uit. We hoeven alleen de vervaldatums vast te leggen om op de laatste dag van de maand te landen.
Nou, het blijkt dat er een coole functie is genaamd EOMONTH (voor het einde van de maand) die de laatste dag van een maand in het verleden of de toekomst krijgt.
Startdatum is de verlengingsdatum en de maanden zijn 12.
En daar heb je het, alles wat je baas wilde, en je hebt nog tijd voor een kopje koffie voor de lunch.
Cursus
KernformuleGerelateerde snelkoppelingen
Tabel invoegen Ctrl + T ⌃ + T Lint uitvouwen of samenvouwen Ctrl + F1 ⌘ + ⌥ + R Tabel selecteren Ctrl + A ⌘ + A Actieve cel met de klok mee verplaatsen in selectie Ctrl + . ⌃ + .
