Easy Office
LCI Learning

Some excel formulas

CA Rishabh Pugalia (ExcelNext) , Last updated: 19 March 2013  
  Share


Case Study 1: Compute 7th of next month

Application area: Computing due dates of statutory payments based on invoice dates. Used to check whether payment has been made on or before due date.

Solution: = EOMONTH(start_date, months)

Example:

= EOMONTH(A7,0) with “0” as parameter helps compute end of Current Month [first picture]. Adding 7 after the end of this formula helps jump the resulting date 7 days ahead [second picture].

Case Study 2: Compute 90 days vs. 3.0 months from Invoice Date

Application area: Computing exact due dates for debtors, tender cut-off date, project deadline

Solution: = EDATE(start_date, months)

Example:

Adding “90” to the Invoice Date will compute due dates based on days. The resulting due date may be different from the due date based on 3.0 months caculation as shown in the second picture.

Case Study 3: Compute Days from given list of Dates.  E.g. Sunday, Monday etc.

Application area:

· Derive day (e.g. Saturday, Sunday) to analyse sales data (day-wise sales trend), locating ghost employees if their date-of-join falls on Sunday, ensuring deadline dates does not fall on a Sunday

· Representing dates in “dd/mm/yyyy” format as required while uploading dates during  e-filing of VAT returns

Solution: =TEXT(value, format_text)

Example:

“format_text”

Resulting Output

dddd

wednesday

ddd

Wed

dd

15

mmmm

June

mmm

Jun

mm

6

yyyy

2011

yyy

2011

yy

11

dd/mm/yyyy

15/07/2011

Watch CA. Rishabh Pugalia's Excel Videos - Click Here


24 Likes   64273 Views

Comments


Related Articles


Loading