Excel Calculations: BrainTrustCheck: Numeric figures for days, months, fortnight, quarter, year?

Hi community. I have noticed discrepancies in calculations based on defined days, weeks, months etc.. and am looking to set up solid figures that can act as the foundation to excel worksheet calculations in the future.

Below are my calculations and formulas. can someone confirm whether these are right? Are there conventions for these type of figures?

 

A quick look appears ok, but hard to tell without seeing the cell numbers.
You could have locked your numerator row reference to make it a bit easier when setting out the formulas but otherwise it’s already done so no harm.

Guessing the variance you may be talking about is when the excel calc varies to yours as you are using an average # days over 4 years (365.25 including leap years).
If you put specific dates into excel I’d expect the actual days would calculate for that year rather than the average over 4 years.

Good luck

 

It would depend what the use is for. eg Bond calculations are different. And payroll is different again and will rely on some legal standards eg 38hours to a week, 52 weeks a year. Interest calculations do not use 365.25 days and use of 365.25 is unorthodox. A year is either 365 or 366 days. Interest calcs are based on this and the actual number of days used based on date ranges and also for the number of days since the prior charge. eg Loan interest date is 15th of month. Calc for March will be 28 days other than a leap year (29) where April will be 31. Excel caters for this in a date format field when a date range calculation is used for accuracy. Depending on use the variance may be immaterial or material.

There are 26 fortnights in most years. Same as some years contain 53 payroll weeks!! And one month each quarter contains 5 weeks, not four and the duration of each quater in days varies. You cant take annual days and divide by 4 to give days per quarter. You cant have a partial fortnight or partial week. Its like having half a birthday.

Tables like this can also yield strange outcomes in some cases. In a specific month there may be different days because a month doesnt start on the same day each month and end on the same day each month. Commonly date ranges are used to ensure actual days in the range are used than a table. These are issues accountants often consider in costing issues and modelling analysis.

I would recommend use of date range formulas