Siste virkedag

December 5  by Eliza

Ved utvikling av et regneark, kan du ha behov for å vite den siste virkedag i en gitt måned. Forutsatt at dine virkedager kjøres mandag til fredag, vil følgende formel returnere ønsket dato:

= DATO (ÅR (A1), MÅNED (A1) +1,0) - (MAX (0, WEEKDAY
(DATO (ÅR (A1), MÅNED (A1) +1,0), 2) -5))

Denne formelen returnerer en dato som er bare en mandag til fredag, og alltid den siste som for dagen i måneden representert av datoen i A1. For noen formål, kan det hende du trenger å vite hva den siste fredagen i en gitt måned er. Dette er lett bestemmes med denne formelen:

= DATO (ÅR (A1), MÅNED (A1) +1,0) -WEEKDAY (DATO
(ÅR (A1), MÅNED (A1) +1,0)) + (UKEDAG (DATO
(ÅR (A1), MÅNED (A1) +1,0)) 5>) * 7-1

Denne formelen beregner den siste dagen i måneden for datoen i celle A1, og basert på hvilken dag i uken at datoen er, subtraherer riktig antall dager til å returnere den forrige fredag.

Hvis du ønsker å ta virksomhet helligdager i betraktning, da kompleksiteten i formelen blir ganske høy, ganske raskt. På grunn av det, er det best å lage en brukerdefinert funksjon (en makro) som vil avgjøre den siste arbeidsdag og kompensere for helligdager.

Følgende makro returnerer en dato, mandag til fredag, som representerer den siste virkedag. Datoen er sammenlignet mot en ferie liste (HolidayList), som skal være en navngitt område i arbeidsboken. Hvis datoen er funnet å være en ferie, så den slutter virkedag dekrementeres inntil en passende dag ligger.

Fungere LastWorkDay (lRawDate Som Long, _
Valgfritt rHolidayList Som Range, _
Valgfri bFriday Som Boolean = False) Som Long

LastWorkDay = Dateserial (Year (lRawDate), _
Måned (lRawDate) + 1, 0) - 0
Hvis bFriday Deretter
LastWorkDay = MakeItFriday (LastWorkDay)
Else
LastWorkDay = NoWeekends (LastWorkDay)
End If

Hvis ikke rHolidayList Er Ingenting Deretter
Gjør Inntil myMatch (LastWorkDay, rHolidayList) = 0
LastWorkDay = LastWorkDay - 1
Hvis bFriday Deretter
LastWorkDay = MakeItFriday (LastWorkDay)
Else
LastWorkDay = NoWeekends (LastWorkDay)
End If
Loop
End If
End Function

Private Function myMatch (vValue, RNG Som Range) Som Long
myMatch = 0
On Error Resume Next
myMatch = Application.WorksheetFunction _
.Match (VValue, RNG, 0)
On Error GoTo 0
End Function

Private Funksjons NoWeekends (lLastDay Som Long) As Long
NoWeekends = lLastDay
Hvis Weekday (lLastDay) = vbSunday Deretter _
NoWeekends = NoWeekends - 2
Hvis Weekday (lLastDay) = vbSaturday Deretter _
NoWeekends = NoWeekends - 1
End Function

Private Function MakeItFriday (lLastDay Som Long) Som Long
MakeItFriday = lLastDay
Mens Weekday (MakeItFriday) <> vbFriday
MakeItFriday = MakeItFriday - 1
Wend
End Function

Legg merke til at det er tre private tilstelninger som er inkludert. Disse funksjonene kalles fra hoved LastWorkDay funksjon. Den første, myMatch, er en "omslaget" for den vanlige Match metoden. Denne bruken er inkludert på grunn av den nødvendige feilhåndtering.

Den andre funksjonen, NoWeekdends, brukes til å sikkerhetskopiere en dato opp til forrige fredag ​​hvis det bare skjer for å være en lørdag eller søndag. Den MakeItFriday funksjonen brukes for å sikre at en dato vil alltid være en fredag.

For å bruke denne brukerdefinert funksjon fra regnearket, bruker du den i en formel, som dette:

= LastWorkDay (A1, HolidayList, TRUE)

Den første parameteren (A1) er datoen som skal evalueres. Den andre parameteren (HolidayList) er en valgfri liste over ferie datoer. Som vist her, er det antatt at HolidayList er et navngitt område i regnearket. Hvis denne parameteren er gitt, og deretter funksjonen sørger for at noen dato den returnerer er ikke på listen over datoer i HolidayList.

Den siste parameter er også valgfritt; det kan være enten SANN eller USANN. (Standard, hvis det ikke er angitt, er FALSK.) Hvis denne parameteren er satt til TRUE, og deretter funksjonen returnerer alltid den siste fredagen i måneden. Hvis denne parameteren er TRUE og HolidayList er gitt, så den funksjonen returnerer den siste ikke-ferie fredag ​​i måneden.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (2452) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: The Last virkedag.