DATEVALUE

 “DATEVALUE”

Used for – “DATEVALUE” function in MS Excel is used to find or convert a date into a serial number that MS Excel recognizes as a date.

Syntax – DATEVALUE(date_text)

             date_text – It is any date/reference cell that contains date, you typed or calculated which you want to convert a number. It is a compulsory field and if you did not fill any value excel will not proceed further.

                       

Example – Following example will clear the DATEVALUE function:-

In below table column A contains some dates in different formats and column G contains the DATEVALUE function.

	A	B	C	D	E	F	G
1
2	=DATEVALUE("01/01/2018")
3	=DATEVALUE("01-JAN-2018")
4	=DATEVALUE("01-JAN")
5	=DATEVALUE("2018/01/01")
6	=DATEVALUE(B6&"/"&C6&"/"&D6)	1	1	2018
7

And result you get as under:-

	A	B	C	D	E	F	G	H	I
1
2	43101
3	43101
4	42736
5	43101
6	43101	1	1	2018
7

NOTE :- Please do care of following points when you use DATEVALUE :-

• Date_text must be entered in double quotation i.e. “”. otherwise it will give #VALUE! error.
• Do not mark any cell whose value is already in date format it will return #VALUE! error.
• When we used only date and month system will consider current year based on the system date. See result of A4 cell. It consider the year 2017 as the system year is 2017.

Read More

AND

“AND”

Used for – “AND” function in MS Excel is used to check that all of the logical test/condition specified are true. This results in TRUE or FALSE which can be used as a condition for further decision.

Syntax – AND(logical1, [logical2], …)

             logical1 – It is any formula/logical test/condition or a reference cell containing result of any logical test in true or false. It is a compulsory field and if you did not fill any value excel will not proceed further.

            [logical2] – This is an optional field it also contains the logical test likewise logical1.

           

Example – Following example will clear the OR function: -

In below table column A, B, & C contains some sales data for particular item in specified cities. Now column G & H contains the OR function with different criteria.

	A	B	C	D	E	F	G
1	City	ITEM	Sale	Qty.
2	New Delhi	Orange	1000	1500		=AND(C2>3000,D2<1250)	=AND(C2<3000,D2>1250)
3	New Delhi	Banana	2000	1250		=AND(C3>3000,D3<1250)	=AND(C3<2000,D3>1250)
4	Muktsar	Papaya	3000	1000		=AND(C4>3000,D4<1250)	=AND(C4<2000,D4>1250)
5	Muktsar	Orange	4000	750		=AND(C5>3000,D5<1250)	=AND(C5<2000,D5>1250)
6	Chandigarh	Banana	5000	500		=AND(C6>3000,D6<1250)	=AND(C6<2000,D6>1250)
7	Chandigarh	Papaya	6000	250		=AND(C7>3000,D7<1250)	=AND(C7<2000,D7>1250)

And result you get as under: -

	A	B	C	D	E	F	G
1	City	ITEM	Sale	Qty.
2	New Delhi	Orange	1000	1500		FALSE	TRUE
3	New Delhi	Banana	2000	1250		FALSE	FALSE
4	Muktsar	Papaya	3000	1000		FALSE	FALSE
5	Muktsar	Orange	4000	750		TRUE	FALSE
6	Chandigarh	Banana	5000	500		TRUE	FALSE
7	Chandigarh	Papaya	6000	250		TRUE	FALSE

Now you can use this result as input for any formula and get result when anyone of the condition is satisfy your specified criteria.

NOTE: - Please do care of following points when you use AND: -

• All the logical provided in the function OR must end with TRUE or FALSE.
• Any logical which end up with empty or blank or anything other than TRUE or FALSE will get ignored.
• If there is no logical ends up with TRUE / FALSE then function will return the #VALUE! error.
• You can enter up to 255 logical at max.

Read More