Voorwaardelijke opmaak met vervaldatum

[Elkana]

Graag zou ik wat hulp willen bij het aanpassen van een documentje. Ik heb al heel wat doorgezocht op het forum maar kan niet vinden wat ik precies nodig heb. In deze thread http://www.helpmij.nl/forum/showthread.php/757796-Voorwaardelijke-opmaak-met-vervaldatum heb ik wel ongeveer gevonden wat ik zoek maar net niet helemaal. Om te begrijpen hoe ik het wellicht zelf aan kan passen wil ik graag weten wat deze formule precies doet:

=ALS.FOUT(ALS(DATUMVERSCHIL(VANDAAG();C2;"m")<3;2;3);1)

Ik zou graag het volgende resultaat bereiken:
Mijn gegevens zijn afloopdata van bepaalde documenten.
De datum '1 maand voor afloop' heb ik berekend door middel van de formule "=B2-31" (de afloopdatum - 31 dagen)

Nu wil ik graag:
- in KOLOM D een groen vinkje als de afloopdatum over 2 maand (vanaf vandaag) verstrijkt.
Met daarachter het aantal dagen dat het nog duurt voordat de afloopdatum is verstreken.

- in KOLOM D een oranje uitroepteken als de afloopdatum over 1 maand (vanaf vandaag)verstrijkt.
Met daarachter het aantal dagen dat het nog duurt voordat de afloopdatum is verstreken.

- in KOLOM D een rood kruisje als de afloopdatum is verstreken (vanaf vandaag).
Met daarachter het aantal dagen dat het geleden is de afloopdatum is verstreken.


Op blad 1 in dit bestand Bekijk bijlage Leverancierslijst_TEST.xlsx staat de tabel waar ik het over heb. Op blad 2 ben ik al wat aan het spelen geweest maar dit is nog niet wat ik wil.

Kunnen jullie me verder helpen?

 

[JeanPaul28]

test deze formule eens afloopdatum - 1 maand
=DATUM(JAAR(B2);MAAND(B2)-1;DAG(B2))

 

[Elkana]

Hallo JeanPaul28,

Dit is 'm nog niet helemaal:

AFLOOP-DATUM
! 30 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 30 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
x 15 januari 2011 (geeft correct aan dat 'ie verlopen is)
x 13 juli 2012 (geeft correct aan dat 'ie verlopen is)
! 13 juni 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
x 30 juni 2011 (geeft correct aan dat 'ie verlopen is)
x 20 juni 2011 (geeft correct aan dat 'ie verlopen is)
! 24 oktober 2013 (geeft correct aan dat 'ie bijna verloopt)
! 26 augustus 2012 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 26 augustus 2012 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 26 augustus 2012 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 11 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
v 2 augustus 2015 (geeft correct aan dat nog niet verlopen is)
! 11 oktober 2013 (geeft correct aan dat 'ie bijna verloopt)
v 2 augustus 2015 (geeft correct aan dat nog niet verlopen is)
x 26 april 2011 (geeft correct aan dat 'ie verlopen is)
x 26 april 2012 (geeft correct aan dat 'ie verlopen is)
! 7 mei 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 4 mei 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 26 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 30 augustus 2012 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 30 augustus 2013 (geeft correct aan dat 'ie bijna verloopt)
v 15 januari 2015 (geeft correct aan dat nog niet verlopen is)
x 2 december 2011 (geeft correct aan dat 'ie verlopen is)
x 8 september 2011 (geeft correct aan dat 'ie verlopen is)
v 8 september 2014 (geeft correct aan dat nog niet verlopen is)
! 7 februari 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)

 

[OctaFish]

Aan de formule mankeert niks: die berekent exact de datum van de vorige maand. Wellicht heb je een vaste termijn in het hoofd, van 31 dagen. Dat is natuurlijk niet gelijk aan 'de vorige maand', want februari heeft nu eenmaal minder dagen dan april of mei. Met een vervaltermijn van 28 dagen zit je overigens een stuk logischer voor mijn gevoel.

 

[Elkana]

Maar hoe kan het dan dat deze formule heel rare dingen doet?

Mijn idee was:
- in KOLOM D een groen vinkje als de afloopdatum over 2 maand (vanaf vandaag) verstrijkt.
Met daarachter het aantal dagen dat het nog duurt voordat de afloopdatum is verstreken.

- in KOLOM D een oranje uitroepteken als de afloopdatum over 1 maand (vanaf vandaag) verstrijkt.
Met daarachter het aantal dagen dat het nog duurt voordat de afloopdatum is verstreken.

- in KOLOM D een rood kruisje als de afloopdatum is verstreken (vanaf vandaag).
Met daarachter het aantal dagen dat het geleden is de afloopdatum is verstreken.

En dan zie ik dat deze formule o.a. de volgende uitslag geeft:
! 30 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 13 juni 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is) - Nou kan ik me voorstellen dat het nog niet de 13e is en dat deze daarom nog een uitroepteken geeft in plaats van 'm te markeren als vervallen.
! 26 augustus 2012 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 11 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 7 mei 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 4 mei 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 26 april 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 30 augustus 2012 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)
! 7 februari 2013 (geeft aan dat 'ie bijna verloopt terwijl het al verlopen is)

Dit heeft toch niks met die maand terug te maken?

Van die 28 dagen is mij ook goed. Het gaat erom dat ik tijd genoeg heb om te zorgen dat ik nieuwe documenten krijg. Een maand leek me mooi maar die is inderdaad variabel dus 28 dagen is dan ook prima. Maar hoe ziet de formule er dan uit?

 

[OctaFish]

Je stelt nu een heel andere probleem; dit is niet meer een formule die 'de datum '1 maand voor afloop'"" berekent; nu wil je een datum relateren aan 1 maand voor vandaag, en 2 maanden voor vandaag. En dat doe je zo:

=DATUM(JAAR(VANDAAG());MAAND(VANDAAG())-1;DAG(VANDAAG()))
=DATUM(JAAR(VANDAAG());MAAND(VANDAAG())-1;DAG(VANDAAG()))

Ik zou die datums een naam geven, bijvoorbeeld Controledatum_1 en Controledatum_2, zodat je in je formules die verwijzing kunt gebruiken.

=ALS(B2<Controledatum_1;"x";"")

 

[Elkana]

Hallo OctaFish,

De opzet van mijn idee is niet veranderd.
Ik kan met de formule

=B2-31

(de afloopdatum - 31 dagen) zelf berekenen wanneer het een maand voor de afloopdatum is.

Ik wil graag -als ik het bestand open- in een oogopslag zien of een document binnen nu (vandaag = dus variabel) en twee en één maand verloopt of dat deze inmiddels verlopen is.

De formule

=ALS.FOUT(ALS(DATUMVERSCHIL(VANDAAG();C2;"m")<3;2;3);1)

komt daarvoor aardig in de buurt maar deze is nog niet helemaal goed.
Deze schijnt het volgende aan te geven:
- datum ligt binnen 12 maanden
- datum ligt binnen 3 maanden van de vervaldatum
- datum ligt buiten de 12 maanden
Maar hoe doet 'ie dit? Ik kan er niks uit opmaken wat deze exact doet.
Als ik weet wat deze formule precies doet kan ik wellicht zelf aan het stoeien gaan.

De code die je hebt gegeven

=DATUM(JAAR(VANDAAG());MAAND(VANDAAG())-1;DAG(VANDAAG()))

geeft bij mij nog steeds fouten. Zie de afbeelding.


Hoe kan ik die datums het beste een naam geven? Via 'Formules' 'Naam bepalen'?

 

[Tides]

Het aantal dagen dat het nog duurt voordat de afloopdatum is verstreken is niet zo moeilijk; dat kan door

B2-VANDAAG()

.

Octafish heeft wel een punt dat je 1 maand voor afloop standaard op 31 dagen hebt gezet. Het zal natuurlijk niet altijd standaard 31 dagen zijn, en daarmee loop je ook in de knoei als je nu 2 maanden wilt berekenen..het is dus logisch dat je dan op kleine verschilletjes komt.

Ik zou het zelf oplossen door NETTO WERKDAGEN te gebruiken.

Bekijk bijlage Leverancierslijst_TEST(1).xlsx

Hier kijk je gewoon wat het aantal werkdagen is tussen de kalender en dan -30 en -60 dagen. Zo weet je in ieder geval welke datum 60 dagen voor je vervaldatum komt en kun je daarop anticiperen. Lijkt mij iets veiliger dan je -31 DAGEN.. want nu worden er werkdagen teruggeteld en voorkom je daarmee verschillen tussen maanden.

Heb je daar wat aan??

 

[cow18]

zie kolommen B en C in bijlage
vervaldatum verder dan 2 maand = groen vinkje
vervaldatum tss 1-2 maand = oranje uitroepteken
vervaldatum < 1 maand = rood kruisje

 

[Elkana]

Hallo Tides,

Dit is geweldig! Helemaal wat ik nodig heb
Ik heb de voorwaardelijke opmaak nu gezet op de kolom restant met de volgende gegevens:

pictogram 'groen vinkje' wanneer de waarde is >= 60 (getal)
pictogram 'oranje uitroepteken' als <60 en >= 30 (getal)
pictogram 'rood kruis' wanneer <30


Dit werkt perfect! Het is ideaal dat ik door middel van de vinkjes, uitroeptekens en kruizen kan zien dat er actie genomen moet worden en hoeveel werkdagen ik nog heb voordat het document verlopen is.

Ik neem aan dat de kolommen 1 maand, 2 maand en de dagen, min 30 en min 60 in het bestand moeten blijven staan maar ik kan ze wel verbergen toch?

Heel erg bedankt!



------------

Oh, zie net dat cow18 het zonder alle tabellen voor elkaar heeft gekregen

Fijn dat jullie de moeite wilden nemen om mij te helpen!

 

[Elkana]

Hoi cow18,

Ik heb de code =ALS.FOUT(B6-VANDAAG();"fout") nu gekopieerd in mijn originele excel maar dan krijg ik allemaal hekjes #########.
Op het vakje krijg ik de melding "Datums en tijden die negatief of te groot voor weergave zijn, worden weergegeven als ########."
Maar waarom is dat?

30 april 2013 geeft als antwoord in de cel ernaast: *rood kruisje* #####
15 januari 2011 geeft als antwoord in de cel ernaast: *rood kruisje* #####
13 juli 2012 geeft als antwoord in de cel ernaast: *rood kruisje* #####
13 juni 2013 geeft als antwoord in de cel ernaast: *rood kruisje* #####
30 juni 2011 geeft als antwoord in de cel ernaast: *rood kruisje* #####
20 juni 2011 geeft als antwoord in de cel ernaast: *rood kruisje* #####
24 oktober 2013 geeft als antwoord in de cel ernaast: *groen vinkje* 13 april 1900

Guttegut... Snap het niet meer. In het voorbeeld werkt 'ie prima maar als ik de formule in een ander bestand zet werkt 'ie weer niet goed...

---------------

Edit: Ik heb het nu overnieuw gedaan en nu werkt het wél weer...

 

[Franssen]

Hoi,
Misschien kan iemand mij helpen....

Ik wil graag gaan bijhouden wanneer een certificaat is verlopen.
Dat ik 1 maand voor de vervaldatum hiervan een oproep kan doen (die ik met een gekoppelde email een herinnering kan verzenden).
Zelf doe ik hele administratie is excel in het bedrijf van mijn man.

Dit is toch wat moeilijk om zo iets samen testellen om dit meteen te zien.

Verder in dit bestand zijn het certificaat nr. en NAW gegevens te zien.

Wie oh wie kan mij helpen.....Ben bekend met Excel maar geen professor.
groetjes,
Franssen

 

[JeanPaul28]

@Franssen
Je zit in iemand anders zijn topic en dat is niet toegestaan, open een eigen topic, je zal dan ook meer reactie krijgen en voeg er ook een voorbeeld bestand bij, en........... welkom op het forum.