I den här videon tittar vi på hur man beräknar och markerar utgångsdatum.
Låt oss säga att ditt företag har startat ett medlemskapsprogram av något slag och din chef skickade dig en uppsättning data. Hon har gett dig en lista över 1000 personer som har förnyat medlemskap under det senaste året eller så, och hon letar efter flera saker.
Först vill hon att du ska beräkna ett utgångsdatum ett år i framtiden, den sista dagen i samma månad som medlemskapet förnyades.
För det andra vill hon se hur många dagar det finns kvar innan utgången.
För det tredje vill hon se statusen "Utgången" för alla medlemskap som redan har upphört att gälla och "Utgå snart" för alla medlemskap som löper ut under de närmaste 30 dagarna.
Slutligen sa hon att det skulle vara trevligt att se utgångna medlemmar markerade i rosa, och de som upphör snart markerade i gult.
Också - kan hon få det före sitt lunchmöte vid middagstid?
Umm, visst.
Låt oss först konvertera dessa data till en ordentlig Excel-tabell. Detta gör det mycket lättare att skriva in formler, eftersom Excel automatiskt kopierar dem när vi går.
Låt oss nu beräkna utgångsdatum. Dessa ska vara i slutet av samma månad ett år senare, men låt oss bara använda ett enkelt hack först för att komma in i ballparken. Som du har sett i tidigare videor är datum bara serienummer, så vi kan bara ange en formel som lägger till 365 dagar till det förnyade datumet.
Det här är en bra start. Vi kan slutföra grovbearbetning av lösningen och komma tillbaka för att åtgärda detta senare.
När du löser ett mer komplext problem i Excel är det en bra idé att validera det övergripande tillvägagångssättet och sedan komma tillbaka till detaljerna i slutet. Du vill inte fastna på en liten sak direkt i början, särskilt om tillvägagångssättet kan förändras.
Nu när vi har ett utgångsdatum kan vi nu beräkna dagar kvar. Detta behöver uppdateras automatiskt i framtiden, så vi använder TODAY-funktionen, som alltid återgår till dagens datum.
Formeln är helt enkelt E5 minus IDAG (). När jag slår tillbaka får vi dagar kvar innan utgången. Negativa siffror indikerar att ett medlemskap redan har löpt ut.
För status använder vi en enkel kapslad IF-formel. Om dagar kvar är mindre än noll upphör medlemskapet. Annars, om dagar kvar är mindre än 30, bör statusen vara "Går snart ut". Annars är statusen ingenting.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Därefter måste vi skapa villkorliga formateringsregler som markerar dessa värden.
Välj först data och ställ in den aktiva cellen i det övre högra hörnet. Skapa sedan en formelregel som testar den aktiva cellen för värdet "Utgått". Kolumnen måste vara låst.
Upprepa samma process för medlemskap som går ut snart.
Det här ser bra ut. Vi behöver bara fixa utgångsdatum för att landa den sista dagen i månaden.
Det visar sig att det finns en cool funktion som heter EOMONTH (för slutet av månaden) som får den sista dagen i en månad i det förflutna eller i framtiden.
Startdatum är datum förnyat och månader är 12.
Och där har du det, allt din chef ville ha, och du har fortfarande tid för en kopp kaffe innan lunch.
Kurs
Core FormulaRelaterade genvägar
Infoga tabell Ctrl + T ⌃ + T Expandera eller komprimera menyfliksområdet Ctrl + F1 ⌘ + ⌥ + R Välj tabell Ctrl + A ⌘ + A Flytta aktiv cell medurs i markeringen Ctrl + . ⌃ + .
