Excel-tutorial: Sådan beregnes og fremhæves udløbsdatoer

I denne video ser vi på, hvordan man beregner og fremhæver udløbsdatoer.

Lad os sige, at din virksomhed har startet et medlemskabsprogram af en eller anden art, og din chef lige har sendt dig et sæt data. Hun har givet dig en liste over 1000 mennesker, der har fornyet medlemskab i det sidste år eller deromkring, og hun leder efter flere ting.

For det første vil hun have dig til at beregne en udløbsdato et år i fremtiden, på den sidste dag i samme måned, hvor medlemskabet blev fornyet.

For det andet vil hun se, hvor mange dage der er tilbage inden udløbet.

For det tredje ønsker hun at se status "Udløbet" for ethvert medlemskab, der allerede er udløbet, og "Udløber snart" for ethvert medlemskab, der udløber inden for de næste 30 dage.

Endelig sagde hun, at det ville være rart at se udløbne medlemmer fremhævet med lyserødt, og dem der udløber snart fremhævet med gult.

Også - kan hun få det før sit frokostmøde kl. 12?

Umm, helt sikkert.

Lad os først konvertere disse data til en ordentlig Excel-tabel. Dette vil gøre formler meget nemmere at indtaste, da Excel automatisk kopierer dem ned, mens vi går.

Lad os nu beregne udløbsdatoer. Disse formodes at være i slutningen af ​​samme måned et år senere, men lad os bare bruge et simpelt hack først for at komme i ballparken. Som du har set i tidligere videoer, er datoer kun serienumre, så vi kan bare indtaste en formel, der tilføjer 365 dage til den fornyede dato.

Dette er en god start. Vi kan færdigbehandle løsningen og komme tilbage for at rette op på det senere.

Når du løser et mere komplekst problem i Excel, er det en god ide at validere den overordnede tilgang og derefter komme tilbage til detaljerne i slutningen. Du ønsker ikke at sidde fast på en lille ting lige i starten, især hvis tilgangen kan ændre sig.

Nu hvor vi har en udløbsdato, kan vi nu beregne tilbage dage. Dette skal opdateres automatisk i fremtiden, så vi bruger TODAY-funktionen, som altid vender tilbage til dagens dato.

Formlen er simpelthen E5 minus I DAG (). Når jeg rammer retur, får vi dage tilbage før udløb. Negative tal angiver, at et medlemskab allerede er udløbet.

Til status bruger vi en simpel indlejret IF-formel. Hvis dage tilbage er mindre end nul, udløber medlemskabet. Ellers, hvis dage tilbage er mindre end 30, skal status være "Udløber snart". Ellers er status intet.

=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))

Dernæst skal vi oprette betingede formateringsregler, der fremhæver disse værdier.

Vælg først dataene, og indstil den aktive celle til øverste højre hjørne. Opret derefter en formelregel, der tester den aktive celle for værdien "Udløbet". Kolonnen skal være låst.

Gentag nu den samme proces for medlemskaber, der udløber snart.

Det ser godt ud. Vi skal bare rette udløbsdatoerne for at lande den sidste dag i måneden.

Nå viser det sig, at der er en cool funktion kaldet EOMONTH (i slutningen af ​​måneden), der får den sidste dag i en måned i fortiden eller fremtiden.

Startdato er dato fornyet, og måneder er 12.

Og der har du det, alt hvad din chef ønskede, og du har stadig tid til en kop kaffe før frokost.

Rute

Kerneformel

Relaterede genveje

Indsæt tabel Ctrl + T ⌃ + T Udvid eller skjul bånd Ctrl + F1 ⌘ + ⌥ + R Vælg tabel Ctrl + A ⌘ + A Flyt aktiv celle med uret i markering Ctrl + . ⌃ + .