Just nu i M3-nätverket
Gå till innehåll
AMI 14

Villkorsstyrd formattering av datum i Excel

Rekommendera Poster

Hej, alla Excelguruar!

 

Jag har en personallista med start datum, slut datum och förlängd anställningsdatum samt en kalender  uppdelad i månader och år (Se bifogad bild).

Behöver  grönmarkera respektive kalendermånader från startdatum till slutdatum och gulmarkera kalendermånader från slutdatum till förlängd datum.

 

Hur får jag till det? Är tacksam för alla tips som för mig vidare i detta.

 

  Consultants_forum.xlsx

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser
Postad (redigerade)

Ja, Men du måste på något sätt få fram ett "riktigt" datum för varje kolumn för att kunna jämföra med start/slutdatum.

 

Och där måste man krångla till det (eftersom du har sammanfogat års-cellerna så måste man förskjuta hämtningen av år på något sätt)

 

Om det bara hade funnits ett år skulle du få startdatumet för varje månadskolumn med hjälp av formeln som klistrat ihop Jan2019, Feb2019 och översätter det till riktiga datum.:

=DATUMVÄRDE(E$4&$E$3)

 

Men du har dessutom engelska beteckningar så Oktober skulle bli fel.  Du får göra en krånglig formel istället.

 

Det här ger hur mycket du skall öka Året

=HELTAL((KOLUMN(E3)-KOLUMN($E$3))/12)

Och det här ger månadens nummer

=PASSA(E$4;$E$4:$P$4;0)

 

Det här ger månads-kolumnernas startdatum för det första året 

=DATUM($E$3;PASSA(E$4;$E$4:$P$4;0);1)

Och det här ger slutet av månaden

=SLUTMÅNAD(DATUM($E$3;PASSA(E$4;$E$4:$P$4;0);1);0)

 

Testa genom att skriva in i E25 respektive E26 och kopiera till höger. Du måste förmodligen ändra visningen till datum

 

Det kan du använda för att skapa dina villkor.

 

Markera E6 

och gå till 

Villkorsstyrd formatering->ny regel->Bestäm vilka celler...formel

i rutan "formatera värden när den här formeln är sann" skriver du in den pyttelilla formeln:

=OCH(DATUM($E$3+HELTAL((KOLUMN(E3)-KOLUMN($E$3))/12);PASSA(E$4;$E$4:$P$4;0);1)>=$B6;SLUTMÅNAD(DATUM($E$3+HELTAL((KOLUMN(E3)-KOLUMN($E$3))/12);PASSA(E$4;$E$4:$P$4;0);1);0)<=$C6)

Klicka på knappen formatera, fliken fyllning, välj en färg.

Ok, ok

 

Behåll markeringen och lägg till en ny regel där du ändrar formeln till:

=OCH(DATUM($E$3+HELTAL((KOLUMN(E3)-KOLUMN($E$3))/12);PASSA(E$4;$E$4:$P$4;0);1)>=$C6;SLUTMÅNAD(DATUM($E$3+HELTAL((KOLUMN(E3)-KOLUMN($E$3))/12);PASSA(E$4;$E$4:$P$4;0);1);0)<=$D6)

Och välj gul färg.

 

För att sprida formateringen markerar du E6 sen kopierar du formatet med hjälp av Penseln "Hämta format"

 

Se bifogad retur

 

OBS!

1. alla månader måste heta samma sak som första året

2. Om du vill förlänga en månad måste du ange sista dagen den månaden, inte första dagen.

Consultants_forum.xlsx

Redigerad av MH_resurrected

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Hej MH_resurrected,

 

Ett jättetack för ditt pedagogiska svar!  Hade inte kommit fram till det utan din hjälp.

 

En fundering: om mitt start- / slutdatum inträffar i mitten av månaden; t.ex. 2010-10-15, vill jag ha den månad markerad (annars markeras det fom november)...Går det att göra det?

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser
Postad (redigerade)

Hej. Tidigare lösning är alldeles för krånglig (jag låste mig vi hur dina data såg ut istället för att tänka...).

 

Så vi börjar om med lite fusk.

I cell E4 så lägger vi in hela datumet 2019-01-01. I cell F4 plussar vi på en månad osv med hjälp av formeln:

=SLUTMÅNAD(E4;0)+1

Kopiera hela vägen till höger (AN4)

Sen markerar du rad 4:a, högerklicka och välj 

Formatera celler->Tal->Anpassat  och skriv in MMM.

Nu ändras visningen så att bara månadsnamnet syns MEN det ligger kvar ett "riktigt" datum i cellen som du kan jämföra dina start/slutdatum med istället för min idiotlösning där du "fiskade ut" varje datum. 

 

Nu formel för grönt (motsvarande föregående jätteformel)

=OCH(E$4>=$B5;E$4<=$C5)

Det blir väl Liiiiiiite lättare att förstå?😁

 

Som du ser låser vi rad 4 ($4) för månadens start-datum  och kolumnen B respektive C för start/slutdatum så att formeln anpassas för varje cell.

 

För att inkludera hela månaden så kan du antingen flytta tillbaks startdatumet till den 1:a i månaden, eller flytta fram kolumnens datum till den sista. 

Eftersom det finns en formel för att flytta fram datumet röstar jag för den lösningen. Sen får du göra samma sak med avslutningsdatumet. Det flyttas till sista dagen i månaden. Så formeln blir marginellt krångligare. 

 

=OCH(SLUTMÅNAD(E$4;0)>=$B5;E$4<=SLUTMÅNAD($C5;0))

Och för gult byter vi B&C Till  C&D.

=OCH(SLUTMÅNAD(E$4;0)>=$C5;E$4<=SLUTMÅNAD($D5;0))

 

Nu kommer alla månader som har minst en dags arbete att färgas.

Om en månad ingår i både grundperioden och i förlängningen markeras den som grundarbete (grön).

Se flik 2 i bifogad

 

PS:

Månadsnamnen I rad 4:a styrs av Windows/Excels landsinställningar. Dvs det blir okt i svensk och oct i engelsk Excel och det är alltid små bokstäver. Det går inte att få Inledande versaler utan att konvertera till text och då förlorar man hela vitsen med datum. Kalla det ett medvetet stilval om någon gnäller…

PS2

Om du verkligen vill ha den 15:e som brytdatum så borde det enklaste vara att kan du lägga till/dra bort 15 dagar när du skapar Kolumnens datum. Typ:

=SLUTMÅNAD(E4;0)+15

Consultants_forum(entire_month).xlsx

Redigerad av MH_resurrected
många orsaker finns det

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Skapa ett konto eller logga in för att kommentera

Du måste vara medlem för att kunna kommentera

Skapa ett konto

Skapa ett nytt konto på vårt forum. Det är lätt!

Registrera ett nytt konto

Logga in

Redan medlem? Logga in här.

Logga in nu



×
×
  • Skapa nytt...