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.