Formula | Chapter 5 | Workday

Workday formula

A very simple formula and much usage too.

In simple terms this formula ascertains a date (past or future), may be a due date.

Syntax

 =Workday(start_date, days, [holidays])

 start_date= A date to represents the start date.

days= A number of working days other than holidays and weekends. Negative days denotes past date.

Above two parameters are required.

holidays = An optional list of one or more dates to exclude from working days.

Formula Workday ILL1

Explanations:

Manual calculations has been made to understand the essence of the formula. Two respective dates in bold (colored) representing two formulas. For the sake of understanding two formulas also written below the dates. I request all to redo the process.

Workday.int Formula

A simple formula with usage.
It determines a date (future or past). May be a due date. 
Very much similar to workday formula.
Syntax
=workday.int(start_date, days, [weekend],[holidays])
start_date = A date.
days = numeric value.
start_date and days are required.
weekend = (1-17)This parameter signifies the counting option. Ordinarily weekend comprises of 'Saturday' and 'Sunday'. Default parameter.
But if one wants to vary the calculation of weekend. They can specify.

1. Saturday, Sunday (Default and can be omitted)
2. Sunday, Monday.



3. Monday, Tuesday. 
4. Tuesday, Wednesday.
5. Wednesday, Thursday.
6. Thursday, Friday.
7. Friday, Saturday.
11,12,13,14,15,16,17
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday,
respectively.

Formula Workday.int ILL2

'Year' Formula

This function/formula returns the year corresponding to a date.

Syntax
=Year(serial_number)
Lets use ILL2
And use the date in cell C4
=Year(C4)
Returns 2020.

'Yearfrac' Formula

This function calculates the number of whole days between two dates. It returns numeric values with fraction where needed.

Syntax

=yearfrac(start_date, end_date, basis)

start_date= A date. (future or past)

end_date= A date. (future or past) 

basis = 0,1,2,3,4

0 - US (NASD) 30/360 (no need to specify can be omitted)

1 - Actual/actual

2 - Actual / 360

3 - Actual / 365

4 - European 30 / 360

Formula yearfrac ILL3

Explanations:

In the ILL1 1.01666667 is the result of the formula =yearfrac(E6, E7, 2) - Basis 2 that signifies Actual / 360.

 

 


 

Formula | Chapter 4 | Time

'TIME' formula

This formula returns the decimal number of time and return value varies from 0 to .99988426.

Syntax
=Time(Hour, Minute, Second)

Here Hour, Minute and Second is represented by 0 to 32767.
In case of hour, if any value is greater than 23 will be divided by 24 and remainder will be treated as hour value. 
Similarly if any value is greater than 59 in case of Minute the same shall be converted into hour and minute.
Again if any value is greater than 59 in case of Second the same shall be converted into hours, minutes and seconds.

Time Formula ILL1

Explanations: 
Go through the above illustration and try it yourself. 
This illustration is self explanatory. 

'TimeValue' Formula

 This formula converts a text to a serial number for time, ranging from 0 to .99998426.

Syntax

=Timevalue(text)

Say

=Timevalue("12:00:45 am")

returns 0.000520833

Instead if we replace am with pm

formula returns 0.500520833

'Today' Formula

This formula returns the current date. Actually it picks up the system date.

A widely used formula.

Syntax

=Today()

Returns the current date - say '28-03-2020'.

 'Weekday' Formula

This formula returns the day of the week corresponding to certain date.
Syntax
=Weekday(serial number, return type)
We can proceed without specifying the return type.
say, 'Weekday(Date)'
The formula returns 1-7 integer only.
It simply specify the day of the date in a week. And return type parameter specify the counting pattern.
Counting syntax (1,2,3,11,12,13,14,15,16,17)
1 - Default 1(Sunday) to 7(Saturday)
2 - 1(Monday) to 7(Sunday)
3 - 0(Monday) to 6(Sunday)
11 - 1(Monday) to 7(Sunday)
12 - 1(Tuesday) to 7(Monday)
13 - 1(Wednesday) to 7(Tuesday)
14 - 1(Thursday) to 7(Wednesday)
15 - 1(Friday) to 7 (Thursday)
16 - 1(Saturday) to 7(Friday)
17 - 1(Sunday) to 7(Saturday)
 

Formula 'Weekday' ILL1

Explanations: 

Column C only represents the basic formula or default formula.
For instance, 15-03-2020 falls on Sunday (consult calendar). And formula returns 1.
Say, the formula with date 21-03-2020 returns 7. Calculating 7 from 15th March and ends on 21-03-2020 (inclusive).
Now if we deviate the calculation from the default calculation.
=Weekday(serial number, return type)
'return type' specifies different calculation option.
Options are specified in 2nd row. And they are 1,2,3,11........17.

 'Weeknum' Formula

This formula returns the week number of a specific date.
The formula returns 1-52 or 53. Since a year is comprised of 52 weeks.
It is bit similar to 'Weekday' function.
Syntax
=Weeknum(serial number, return type)
Return type (optional) determines on which day the week begins.

 

Formula 'Weeknum' ILL2

Explanations:
Return type here denotes numeric values 1,2,11,12.....21.
For instance numeric value 15 denotes..... week counting starts from 'Friday'. Hence the result varies.