dag week en jaar omzetten in datum bij excel

[bram2488]

Mijn vraag

Kan ik in excel een dag/week/jaar omzetten in een datum

In onze branche werken we met dag/week/jaar bij het plannen van een levering

Bijvoorbeeld schoen van klant x klaar op di/50/22 (dinsdag week 50 2022)

Wat ik wil bereiken is dat als deze dag nadert 1 week van te voren een rode cel genereren bij deze klantnaam. Ik weet dat je met een formule dit kan bewerkstelligen (nu even ontschoten maar met wat zoekwerk moet dat te doen zijn denk ik) Ik krijg het niet voor elkaar om excel met weken/dagen/jaren te laten werken en dan de cel rood te laten maken bij het bereiken van 1 week voor de geplande leverdatum.

Hiermee wordt voor mij duidelijk dat een bestelling misschien te laat aankomt, dat geeft mij de tijd om leverancier te contacten en evt klant te informeren als nodig.

[dixet]

Je zal dit in een aantal stappen moeten doen

1. opsplitsen van je datum-aanduiding naar de losse elementen. Dat gaat het makkelijkst met TEXTSPLIT
2. Je dag-van-de-week aanduiding omzetten naar een nummertje. Daarvoor kan je een lijstje maken met de waarden ma t/m zo met daarnaast de nummers 1 t/m 7. Dan zoek je met XLOOKUP de dagnaam op en krijgt het nummertje terug
3. Voor de zekerheid het jaartal omzetten naar een volledig, 4-cijferig, jaar
4. Het weeknummer omzetten in een datum. Deze formule berekent de maandag van de betreffende week
   code:
   

   =DATE(A1; 1; -3 + 7 * B1 - WEEKDAY(DATE(A1; 1; 4); 2) + 1).

   Hier staat in A1 het volledige jaartal en in B1 het weeknummer
5. Tot slot tel je het in stap 2 gevonden dagnummer van de gezochte dag (minus 1) op bij deze datum

De makkelijkere manier zou natuurlijk zijn om af te spreken gewoon te plannen met een echte datum te werken in plaats van dagnaam/weeknummer/jaar

[dix-neuf]

bram2488 schreef op vrijdag 25 november 2022 @ 22:01:
Kan ik in excel een dag/week/jaar omzetten in een datum? In onze branche werken we met dag/week/jaar bij het plannen van een levering. Bijvoorbeeld schoen van klant x klaar op di/50/22 (dinsdag week 50 2022). Wat ik wil bereiken is dat als deze dag nadert 1 week van te voren een rode cel genereren bij deze klantnaam.

Als in C2 (bijvoorbeeld) staat: di/47/22, stel dan in als voorw. opmaak met opvulkleur rood voor die cel:

code:

=--(deel($C2;4;2))>=weeknummer(vandaag())-1+(jaar(vandaag())-(2000+rechts($C2;2)))*52

Het gedeelte na -1 is toegevoegd om de formule ook te laten werken als bv. de huidige datum in 2022 en de opleverdatum in 2023 ligt. Als dat het geval is, is de werking van de formule wel afhankelijk van het 'soort' weeknummer dat je gebruikt (Amerikaans-, iso-, of ander weeknummer). In de formule is uitgegaan van een jaar met 52 weken. Pas aan indien nodig.
Als de opleverdatums in dezelfde kolom onder elkaar staan, kun je de voorw. opmaak van C2 naar beneden kopiëren in kolom C. In het voorbeeld (met in C2 di/47/22) zou de cel niet rood moeten kleuren, maar zodra je het getal 47 wijzigt in 48, wel (als wordt uitgaan van de dag van vandaag: 28-11-2022).

[bram2488]

dix-neuf schreef op maandag 28 november 2022 @ 13:03:
[...]

Als in C2 (bijvoorbeeld) staat: di/47/22, stel dan in als voorw. opmaak met opvulkleur rood voor die cel:

code:

=--(deel($C2;4;2))>=weeknummer(vandaag())-1+(jaar(vandaag())-(2000+rechts($C2;2)))*52

Het gedeelte na -1 is toegevoegd om de formule ook te laten werken als bv. de huidige datum in 2022 en de opleverdatum in 2023 ligt. Als dat het geval is, is de werking van de formule wel afhankelijk van het 'soort' weeknummer dat je gebruikt (Amerikaans-, iso-, of ander weeknummer). In de formule is uitgegaan van een jaar met 52 weken. Pas aan indien nodig.
Als de opleverdatums in dezelfde kolom onder elkaar staan, kun je de voorw. opmaak van C2 naar beneden kopiëren in kolom C. In het voorbeeld (met in C2 di/47/22) zou de cel niet rood moeten kleuren, maar zodra je het getal 47 wijzigt in 48, wel (als wordt uitgaan van de dag van vandaag: 28-11-2022).

Ik doe iets verkeerd denk ik, het is nu week 48, als ik jouw formule invul bij opmaak met formule gebeurd er verder niets als ik bijvoorbeeld do/48/22 in cel C2 zet (dat is minder dan 1 week voor de leverdatum.

Of moet ik dit ergens anders invullen?

[ Voor 11% gewijzigd door bram2488 op 28-11-2022 22:22 . Reden: Nu met plaatje van excel ]

[dix-neuf]

In mijn vorig bericht schreef ik:

In het voorbeeld (met in C2 di/47/22) zou de cel niet rood moeten kleuren, maar zodra je het getal 47 wijzigt in 48, wel (als wordt uitgaan van de dag van vandaag: 28-11-2022).
En ook schreef ik: "dat de werking van de formule afhankelijk is van het 'soort' weeknummer dat je gebruikt (Amerikaans-, iso-, of ander weeknummer)."

Welnu: voor Excel is vandaag (29-11-2022) het huidige weeknummer (week van zondag 27-11-2022 t/m zaterdag 03-12-2022) week 49 ! Dat kun je eenvoudig controleren door in een willekeurige cel in te voeren: =weeknummer(vandaag()) .

Ik vermoed dat jij het iso-weeknummer gebruikt, dat 'kent' Microsoft Excel vanaf versie 2013.
Wijzig in de formule daarom weeknummer in iso.weeknummer, de formule voor voorw. opmaak komt er dan zo uit te zien:

code:

=--(deel($C2;4;2))>=iso.weeknummer(vandaag())-1+(jaar(vandaag())-(2000+rechts($C2;2)))*52

Let wel: de formule kijkt alleen naar een verschil in weeknummers, niet naar een verschil in dagen !
NB: De datum 01-01-2023 is 'voor iso' nog weeknummer 52, die van 02-01-2023 weeknummer 1.
Laat a.u.b. even weten of deze oplossing voldoet.

[ Voor 5% gewijzigd door dix-neuf op 29-11-2022 06:08 ]

[bram2488]

dix-neuf schreef op dinsdag 29 november 2022 @ 05:17:
In mijn vorig bericht schreef ik:

In het voorbeeld (met in C2 di/47/22) zou de cel niet rood moeten kleuren, maar zodra je het getal 47 wijzigt in 48, wel (als wordt uitgaan van de dag van vandaag: 28-11-2022).
En ook schreef ik: "dat de werking van de formule afhankelijk is van het 'soort' weeknummer dat je gebruikt (Amerikaans-, iso-, of ander weeknummer)."

Welnu: voor Excel is vandaag (29-11-2022) het huidige weeknummer (week van zondag 27-11-2022 t/m zaterdag 03-12-2022) week 49 ! Dat kun je eenvoudig controleren door in een willekeurige cel in te voeren: =weeknummer(vandaag()) .

Ik vermoed dat jij het iso-weeknummer gebruikt, dat 'kent' Microsoft Excel vanaf versie 2013.
Wijzig in de formule daarom weeknummer in iso.weeknummer, de formule voor voorw. opmaak komt er dan zo uit te zien:

code:

=--(deel($C2;4;2))>=iso.weeknummer(vandaag())-1+(jaar(vandaag())-(2000+rechts($C2;2)))*52

Let wel: de formule kijkt alleen naar een verschil in weeknummers, niet naar een verschil in dagen !
NB: De datum 01-01-2023 is 'voor iso' nog weeknummer 52, die van 02-01-2023 weeknummer 1.
Laat a.u.b. even weten of deze oplossing voldoet.

Ik heb m aangepast, werkt nu wel, iets beter lezen

Maar ik wil graag dat de cel rood kleurt als de productiedatum 1 week of minder dan 1 week in de toekomst ligt en eventueel als het in het verleden ligt.

Geen idee of dat ook kan, wordt misschien wel heel ingewikkeld.

Als voorbeeld (uitgaande van vandaag 29-11)

di/47/22 (ligt in het verleden, mag rood zijn)
do/48/22 (is minder dan 1 week in de tekomst wordt rood)
do/49/22 (meer dan 1 week in de tekomst hoeft niet rood)

Dan heb ik overzicht welke bestelling op minder dan 1 week in de toekomst nog niet binnen is bij ons filiaal.

[HikariMisako]

bram2488 schreef op dinsdag 29 november 2022 @ 13:29:
[...]


Ik heb m aangepast, werkt nu wel, iets beter lezen

Maar ik wil graag dat de cel rood kleurt als de productiedatum 1 week of minder dan 1 week in de toekomst ligt en eventueel als het in het verleden ligt.

Geen idee of dat ook kan, wordt misschien wel heel ingewikkeld.

Als voorbeeld (uitgaande van vandaag 29-11)

di/47/22 (ligt in het verleden, mag rood zijn)
do/48/22 (is minder dan 1 week in de tekomst wordt rood)
do/49/22 (meer dan 1 week in de tekomst hoeft niet rood)

Dan heb ik overzicht welke bestelling op minder dan 1 week in de toekomst nog niet binnen is bij ons filiaal.

Als je 2 cellen hebt die als datum gesnapt worden dan is het niet ingewikkeld om het verschil daartussen te pakken. Even simpel voorbeeld met een extra kolom:

code:

=(ABS(CelA - CelB))>7

Die levert TRUE/FALSE op. Als het goed is kan je met conditioneel formatteren dit ook verwerken met een formule.

[dix-neuf]

Klik boven het lint op Formules, en kies dan in het lint: Namen beheren.
Klik in het verschijnende venster op Nieuw,
vul bij "Naam" in: Dagen, bij "Bereik": Werkmap,
en bij "Verwijst naar":

={"ma";"di";"wo";"do";"vr";"za";"zo"}

Klik dan op Ok.

Stel in als voorw. opmaak voor $C2 met kleur rood, deze formule:

code:

=of(--deel($C2;4;2)+(jaar(vandaag())-2000-rechts($C2;2)<>0)*52<=iso.weeknummer(vandaag());en(--deel($C2;4;2)=iso.weeknummer(vandaag())+1;vergelijken(links($C2;2);dagen;0)<weekdag(vandaag();2)))

Die voorw. opmaak kun je naar de onderliggende cellen kopiëren (of meteen bij $C2 instellen dat die vw. opmaak voor (bijv.) $C2:$C50 moet gelden). Dan heb je - denk ik - wat je wil, namelijk:
alle cellen met een datum in het verleden en met een datum die kleiner is dan die van (vandaag + 1 week), kleuren rood. Dat is wat je in je laatste bericht vroeg.
Laat a.u.b. even weten of dit voldoet.

[bram2488]

dix-neuf schreef op donderdag 1 december 2022 @ 03:02:
Klik boven het lint op Formules, en kies dan in het lint: Namen beheren.
Klik in het verschijnende venster op Nieuw,
vul bij "Naam" in: Dagen, bij "Bereik": Werkmap,
en bij "Verwijst naar":

={"ma";"di";"wo";"do";"vr";"za";"zo"}

Klik dan op Ok.

Stel in als voorw. opmaak voor $C2 met kleur rood, deze formule:

code:

=of(--deel($C2;4;2)+(jaar(vandaag())-2000-rechts($C2;2)<>0)*52<=iso.weeknummer(vandaag());en(--deel($C2;4;2)=iso.weeknummer(vandaag())+1;vergelijken(links($C2;2);dagen;0)<weekdag(vandaag();2)))

Die voorw. opmaak kun je naar de onderliggende cellen kopiëren (of meteen bij $C2 instellen dat die vw. opmaak voor (bijv.) $C2:$C50 moet gelden). Dan heb je - denk ik - wat je wil, namelijk:
alle cellen met een datum in het verleden en met een datum die kleiner is dan die van (vandaag + 1 week), kleuren rood. Dat is wat je in je laatste bericht vroeg.
Laat a.u.b. even weten of dit voldoet.

Bedankt voor je heldere uitleg! Ik heb het nu helemaal zoals ik wil