Dato funksjoner

Meget knapphendig beskrivelse du kommer med.
Har du en tabell med data over et år og vil få fram data fra aktuell uke så kan du jo bruke autofilter og filtrere på dato fra til.

Jeg har prøvt og gjette meg til va du vil ha og laget et ark med formler utifra det.

Så bra at det virket.
Her er månedsformel:

Formula:

Please Login or Register  to view this content.

Apologies in advance for my Norwegian.....

inneværende uke.....

=IF(ABS(C2-WEEKDAY(C2,3)+3-TODAY())<4;1;0)

inneværende måned

=IF(C2-DAY(C2)=TODAY()-DAY(TODAY());1;0)

Kjempesmarte formler der, daddylonglegs. Månedsformeln var riktigt flott og lett of skjønne. Ukeformeln var bare litt før komplisert for meg akkurat nå.



Jeg insåg plutselig at dette konseptet er jo enkelt og greit og skjønne. Hvorfor fann jeg ikke på det med en gang?

Uke

Formula:

Please Login or Register  to view this content.

Måned

Formula:

Please Login or Register  to view this content.

Hello Jacc, I won't try to explain in Norwegian.....

In this formula

=IF(ABS(C2-WEEKDAY(C2,3)+3-TODAY())<4;1;0)

C2-WEEKDAY(C2;3)+3 will always give the Thursday date within the same week as C2. As Thursday is the midpoint of the week then if you subtract today's date from that date the difference (+ or -) will always be within 3 days if today is in the same week, hence:

=ABS(C2-WEEKDAY(C2;3)+3-TODAY())<4

I'd be wary about using your WEEKNUM version because it won't work when a Monday - Sunday week spans two different calendar years

Good explanation, thanks!
Yes you are of course right about the week number system, it can't be trusted. The ISO week system is ignored by most and certainly by Excel.

You can get ISO week numbers in Excel 2010 with

=WEEKNUM(Date,21)

or it's more explicit in Excel 2013 with

=ISOWEEKNUM(Date)

but even with those you can't easily know that date is also in the same "year"........