Is there a formula or function for Numbers that returns the number of days or weeks for a given year

Question

I have a budget table with cost per week, fortnight, month, quarter and year. Some expenses are charged quarterly and others monthly or weekly, but I want to know what the value of a cell is for the week, month etc. using a more efficient, preferably inbuilt function to return the week in the formula.

For example, car registration is done quarterly but I break it down to week, fortnight, month and year using manual formulas.

Cost per week is =SUM(quarter * 4 / 52.14) ...

Of course this year (2016) is a leap-year so I had to go through and update all my formulas from 52.14 weeks in a year to 52.28.

There has to be a way to use a function or formula that works out the weeks in a year. I hope this question isn't to ambiguous but I'm just applying what I know about maths, not spreadsheets in general. I realise that it is a simple task to only do once every 4 years but I'd just like to know.

Answer 1

Try using the DATEDIF(start-date,end-date,calc-method) divided by 7 to determine number of weeks.

For example:

DATEDIF returns the number of days between to dates. Dividing that by 7 should result in the number of weeks. This can be rounded as desired and calculated against.

From the Formula reference:

The DATEDIF function returns the number of days, months, or years between two dates.
DATEDIF(start-date, end-date, calc-method)
start-date: The starting date. start-date is a date/time value (the time portion is ignored) or date string value.
end-date: The ending date. end-date is a date/time value (the time portion is ignored) or date string value.
calc-method: A modal value that specifies how to express the time difference and how dates in different years or months are handled.
“D”: Return the number of days between the start and end dates.
“M”: Return the number of months between the start and end dates.
“Y”: Return the number of years between the start and end dates.
“MD”: Return the days between the start and end dates, ignoring months and years. The month in end-date is considered to be the month in start-date. If the starting day is after the ending day, the count starts from the ending day as if it were in the preceding month. The year of the end-date is used to check for a leap year.
“YM”: Return the number of whole months between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.
“YD”: Return the number of days between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.