Excel logbook for sars
If you are looking to create an Excel logbook for SARS and you want it to include the dates for working days only, you can easily create it in Excel using the WORKDAY function. Using this function means you that you won’t need to enter every date and it can even take into account public holidays.
The process itself is fairly easy. Open a new workbook and in cell A2 enter the start date e.g. 1 Jan 2014.
In cell C2 to C15 list the public holidays you want to take into account.
Now in Cell A3 type the following formulae
The formula above will start at 1 Jan 2014 and will show you the date of the next workday. This formula assumes that Saturday and Sunday are non work days and also assumes that the public holidays listed in C2 to C15 are non work days. To understand how it works watch the WORKDAY video clip
You can now copy the formula down as far as you want and it will show only the upcoming workdays. Then to make it even more useful, format the date to include the day of the week (click on the date cells, right click and click on FORMAT CELLS, click on CUSTOM and in the dialogue box type dd mmm yyyy dddd)
If you work in an industry where Saturday and/ or Sundays could be work days e.g. a restaurant and you want for example a Monday to be treated as a ‘weekend’ then you need to make use of the WORKDAY.INTL function. All it asks for is an additional option of what is considered a weekend.
So the syntax as shown below is exactly the same except for the Weekend option
WORKDAY.INTL(Start Date, Days, [Weekend],[Holidays])
For the Weekend option you can enter a number from 1 to 17 which tells Excel what to consider a weekend i.e.
- 1 or omitted = Saturday, Sunday
- 2 = Sunday, Monday
- 3 = Monday, Tuesday
- 4 = Tuesday, Wednesday
- 5 = Wednesday, Thursday
- 6 = Thursday, Friday
- 7 = Friday, Saturday
- 11 = Sunday only
- 12 = Monday only
- 13 = Tuesday only
- 14 = Wednesday only
- 15 = Thursday only
- 16 = Friday only
- 17 = Saturday only
To better understand dates have a look at the ebook on Handling Dates in Excel