Assignment 14 Date Function Product Status

 

Assignment 14

Date Function Product Status

Function Use

IFERROR :- The Iferror function manage error in formula and calculation to an error, returns another value you specified otherwise return the result of the formula.

DATEDIF :- The datedif function returns the difference between two dated values in years, months, or days.

[DATE +Difference]

TODAY :- The today function return the current date and updated continuously when a worksheet is change or opened.

Function For "Status"

=IF(E3>TODAY(),"USE","Expire")

This Function use for check the medicine in usable /Expire.

If the medicine manufacture date is greater than todays date than it will be print "USE" otherwise "Expire"

Function For "Days Left"

=IFERROR(DATEDIF(TODAY(),E3,"y")&"Year"&DATEDIF(TODAY(),E3,"ym")&"Month"&&DATEDIF(TODAY(),E3,"md")&"Days","Expire")

IFERROR:- This function return a value you specify if a formula evaluates to an error otherwise it will be return result of the formula.

DATEDIF :- This function return the difference between two date value.

TODAY :- This function return the current system date.

Note :- "Y" for print year and "ym" for Month the next is "md" for days.

Function for "Expire days"

=IF(F3="USE","USE",DATEDIF(D3,E3,"y")&"Year"&DATEDIF(D3,E3,"ym")&"Month"&DATEDIF(D3,E3,"md")&"Day")

Note :-

In this sheet you have to find the status of the medicines are they in use or not and find how many days are remain if the product is in use and use conditional formatting if expire, use any function you need without VBA. You can change the Date format of column D (Expire).