I denne videoen vil vi se på hvordan man beregner og fremhever utløpsdatoer.
La oss si at firmaet ditt har startet et medlemskapsprogram av noe slag, og sjefen din nettopp sendte deg et sett med data. Hun har gitt deg en liste over 1000 personer som har fornyet medlemskapet det siste året eller så, og hun ser etter flere ting.
Først vil hun at du skal beregne en utløpsdato ett år i fremtiden, på den siste dagen i samme måned medlemskapet ble fornyet.
For det andre vil hun se hvor mange dager som er igjen før utløpet.
For det tredje ønsker hun å se statusen "Utløpt" for ethvert medlemskap som allerede er utløpt, og "Utløper snart" for ethvert medlemskap som utløper de neste 30 dagene.
Til slutt sa hun at det ville være hyggelig å se utgåtte medlemmer uthevet i rosa, og de som utløper snart uthevet i gult.
Også - kan hun få det før lunsjmøtet sitt på middagstid?
Umm, visst.
La oss først konvertere disse dataene til en skikkelig Excel-tabell. Dette vil gjøre formler mye lettere å legge inn, siden Excel automatisk kopierer dem ned mens vi går.
La oss nå beregne utløpsdatoene. Disse skal være på slutten av samme måned ett år senere, men la oss bare bruke et enkelt hack først for å komme i ballparken. Som du har sett i tidligere videoer, er datoer bare serienumre, så vi kan bare skrive inn en formel som legger til 365 dager til den fornyede datoen.
Dette er en god start. Vi kan ferdigstille løsningen og komme tilbake for å fikse dette senere.
Når du løser et mer komplekst problem i Excel, er det en god ide å validere den generelle tilnærmingen, og deretter komme tilbake til detaljene på slutten. Du vil ikke bli sittende fast på en liten ting helt i starten, spesielt hvis tilnærmingen kan endres.
Nå som vi har en utløpsdato, kan vi nå beregne dager igjen. Dette må oppdateres automatisk i fremtiden, så vi bruker TODAY-funksjonen, som alltid returnerer til dagens dato.
Formelen er ganske enkelt E5 minus I DAG (). Når jeg treffer retur, får vi dager igjen før utløpet. Negative tall indikerer at et medlemskap allerede er utløpt.
For status bruker vi en enkel nestet IF-formel. Hvis dager igjen er mindre enn null, er medlemskapet utløpt. Ellers, hvis dager igjen er mindre enn 30, bør statusen være "Utløper snart". Ellers er status ingenting.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Deretter må vi lage betingede formateringsregler som fremhever disse verdiene.
Velg først dataene, og sett den aktive cellen i øvre høyre hjørne. Opprett deretter en formelregel som tester den aktive cellen for verdien "Utløpt". Kolonnen må være låst.
Gjenta nå samme prosess for medlemskap som utløper snart.
Dette ser bra ut. Vi trenger bare å fikse utløpsdatoene for å lande den siste dagen i måneden.
Vel, det viser seg at det er en kul funksjon som heter EOMONTH (for slutten av måneden) som får den siste dagen i en måned i fortiden eller fremtiden.
Startdato er dato fornyet, og måneder er 12.
Og der har du det, alt sjefen din ønsket, og du har fortsatt tid til en kopp kaffe før lunsj.
Kurs
KjerneformelRelaterte snarveier
Sett inn tabell Ctrl + T ⌃ + T Utvid eller skjul bånd Ctrl + F1 ⌘ + ⌥ + R Velg tabell Ctrl + A ⌘ + A Flytt aktiv celle med klokken i valg Ctrl + . ⌃ + .
