Excel - spreadsheet modeling module 1

Excel samenvatting workshop 2.1
Een serial number is een uniek getal dat aan een item wordt toegekend (hier: datum/tijd).
Verander je de getal opmaak dan blijft het een getal, maar dan is het simpelweg verstopt
door de gekozen getal opmaak.

Datums (data) & datum functies
Als je een datum als tekst intypt in Excel, zet Excel de datum automatisch om (1-jan-2020 of
1/1/2020). Nog een aantal manieren om een datum als tekst te krijgen:

=DATEVALUE(date_tekst) =DATEVALUE(B34&"/"&C34&"/"&D34) en kent geen overflow
=DATE(year;month;day) date werkt wel met overflow, handig als je een aantal dagen moet
optellen =DATE(2020;13;1) wordt automatisch 2021;1;1.
=TODAY() de datum van vandaag
=NOW() de datum van vandaag en de huidige tijd

=YEAR(date) neemt een datum (serial number) als invoer en geeft het jaar als uitvoer
=MONTH(date) neemt een datum als invoer en geeft de maand als uitvoer (1, 12)
=DAY(date) neemt een datum als invoer en geeft de dag als uitvoer (1, 31)
=ISOWEEKNUM(date) neemt een datum als invoer en geeft het weeknummer als uitvoer
=WEEKDAY(date;[return type]) neemt als datum een invoer en geeft het dag nummer als
uitvoer. Return type is hoe je wilt dat Excel de zeven dagen van de week weergeeft.
=EDATE(date;k) neemt een datum als invoer en telt er k maanden bij op (of af). Wil je een
aantal dagen optellen bij een datum: =C4+1 of met jaren: =EDATE(C4;12*10) dit kan je ook
doen door: =DATE(YEAR(C36)+240), MONTH(C36), DAY(C36)).
=EOMONTH(date;k) is hetzelfde als EDATE, maar geeft altijd het einde van de maand weer
=EOMONTH(C4;0) is de laatste dag van deze maand

Dagen tussen 2 datums
Door onregelmatigheden in jaren, maanden en dagen kan het risicovol zijn om te rekenen
met datums. Excel houdt rekening met deze onregelmatigheden. Je kan twee datums van
elkaar aftrekken als beide datums hele serial numbers zijn. Je kan afrondingsproblemen
krijgen wanneer je serial number een decimal getal is. Andere opties zijn:

=DAYS(end_date;start_date) om het aantal dagen te berekenen tussen twee data
=DATEIF(start_date;end_date;unit) werkt wel, maar =DAYS is veiliger

=NETWORKDAYS(start_date;end_date;[holidays]) berekent het aantal werkdagen tussen
twee datums in, rekening gehouden met vakanties. Holidays is het celbereik dat de
vakantiedatums bevat
=NETWORKDAYS.INTL(start_date;end_date;[weekend];[holidays] berekent het aantal
werkdagen tussen twee datums en houdt rekening met vakanties en weekenden

Er zijn ook gerelateerde functies. NETWORKDAYS geven het aantal werkdagen (start date +
end date) en WORKDAY geven de eind datum (start date + aantal workdays).
=WORKDAY(start_date;k;[holidays]) neemt een datum als invoer en telt hier k werkdagen bij
op, terwijl het rekening houdt met vakanties
=WORKDAY.ITL(start_date;k;[weekend];[holidays] neemt een datum als invoer en telt k
werkdagen bij op, terwijl het rekening houdt met vakanties en het weekend

=DATEDIF(cel1 – cel2; “d” “m” “y”) gebruik je om het verschil uit te rekenen in
dagen/maanden/jaren.

, Tijd & tijd functies
Excel geeft de tijd ook als getal, dit gebeurt met uren:minuten:secondes. Je kan de tijd als
serial number krijgen door de tijd in te typen als tekst. Nog een aantal manieren om een tijd
als tekst te krijgen:

=TIMEVALUE(time_tekst) schrijf je in Excel als =TIMEVALUE(B12&":"&C12&":"&D12)
=TIME(hour;minute;second) schrijf je in Excel als: =TIME(B12;C12;D12)
Om een tijd weer te geven kijkt Excel alleen naar het decimale gedeelte; 1,5 en 0,5
representeren beide 12:00. TIMEVALUE zet de originele tekst om naar een nummer tussen
0 en 1, terwijl VALUE dat niet doet. Excel kan niet omgaan met meer dan één overflow.

=NOW()+"1:00" is de huidige tijd +1 uur
=NOW() - "0:2:30" is de huidige tijd -2 en een halve minuut

=HOUR(time) neemt een tijd (serial number) als invoer en geeft het uur als uitvoer (0, 23)
=MINUTE(time) neemt een tijd als invoer en geeft de minuut als uitvoer (0, 59)
=SECOND(time) neemt een tijd als invoer en geeft de seconde als uitvoer (0, 59)
Bovenstaande functies rekenen ook met overflow.

=VALUE(time_text) Is heel handig bij het rekenen met overflow. Je kan hier ook werkuren bij
elkaar op- of aftrekken. Let altijd op: 1 is 24 uur en 1.5 is 36 uur. De vraag: ‘deze week heb ik
15:45 + 15:45 uren gewerkt’, bereken je dat door =VALUE(“15:45”+”15:45”).

Tijd tussen 2 tijdstippen & afronden
Bij het rekenen met twee tijdstippen vermenigvuldig je de tijd met 24 (aantal uren). Dus: ‘ik
heb gewerkt van 7:38 (AM) tot 16:52 (4:52 PM) en wil het aantal uren in decimalen
uitrekenen, doe je door: (“4:52 PM” – “7:38 AM”)*24 = 9.23. Bij een nachtdienst kan dit niet.
Je kan een aantal functies gebruiken:
If start tijd < eind tijd  eind tijd – start tijd of else (eind tijd + 1) – start tijd

MOD(eind_tijd-start_tijd;1) doet bovenstaande mogelijkheden in een formule. Een voorbeeld:
=MOD(“20:00”-“04:00”;1) je zet hem dan op general om een getal te krijgen.
Dit staat gelijk aan MOD(n, d) = n-d*int(n/d)

Afronden van een cijfer; het getal erachter is je aantal decimalen; 0 betekent geen
=ROUND(E14;0) rond af naar het dichtstbijzijnde getal
=ROUNDUP(E14;0) rond af naar boven
=RONDDOWN(E14;0) rond af naar beneden

Afronden van een bedrag
=MROUND(a;m) rond het getal a naar het dichtstbijzijnde veelvoud van m af MROUND(eind-
begin, “”00:05”) een alternatief is: =ROUND(B13/”00:05”,0*”00:05”
=CEILING(B12;”00:05”) rond af naar boven, kan ook: =CEILING(B12;10) op tientallen
=FLOOR(B12;”00:05”) rond af naar beneden, kan ook: =FLOOR(B12;10) op tientallen

Datum & tijd opmaken
Je kan je opmaak voor tijd en datum in Excel kiezen door naar het number formats drop-
down menu te gaan, te kiezen voor ‘more number formats’ en te kiezen voor de opmaak
‘date’, ‘time’ of ‘custom’. Je kan ook een functie gebruiken om een opmaak te forceren. D =
dag als een getal, dd = dag als een getal (altijd twee cijfers), ddd = afkorting van de dag en
dddd is de naam van de dag. Hetzelfde geldt voor maanden en jaren yy (19), yyyy (2019).
Je kan kiezen uit verschillende opmaak opties:
=TEXT(“01/01/2020”;”m-yy”) is 1-20
=TEXT(“01/01/2020”;”dddd dd mmm”) is Wednesday 01 jan
=TEXT(“01/01/2020”;”dddd, mmmmm dd, yyyy”) is Wednesday, january 01, 2020