# Some excel formulas

CA Rishabh Pugalia (ExcelNext) , 19 March 2013

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

Tags :

Category Students, Other Articles by - CA Rishabh Pugalia (ExcelNext)