CA Rishabh Pugalia (ExcelNext)
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
CAclubindia
