Free Exercises for Excel tips and tutorial - Function according to the week

Date & Time - Function according to the week

1. Other date functions are very useful. For instance, you have the function Weekday

Formule de la cellule

=WEEKDAY(<I>Date</I>;Return_Type)

2. This function return a figure corresponding to the week day. The parameter Return_Type 1 means that the week start a Sunday 2 means that the week start a Monday 3 means that the week start a Monday but with the value 0

3. For use the next function, you must activate in the menu 'Tools/Add-Ins ...' the option Analysis ToolPak

4. You can used the function WEEKNUM to find the the week number (logic :p) but this function is not useful in Europe

5. Indeed, if you leave in Europe or in USA, the week number could be different.

For the European country, you should use the following formula

Formule de la cellule

=INT((A2-(DATE(YEAR(A2-WEEKDAY(A2-1)+4);1;3)-WEEKDAY(DATE(YEAR(A2-WEEKDAY(A2-1)+4);1;3)))+5)/7)

6. Another very useful function is :

Formule de la cellule

=NETWORKDAYS(first date;last date;holiday)

7. Of course, you have to create a spread sheet with your day off. The opposite example show you the formula to find the variable day off

Excel-Exercise-Free