1

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.

  • Just to be clear, are you asking how to determine the week number for a given date? By 'return the week in the formula' do you mean the week number? I.e: today 2016-01-14 is in week number 3? Or would this help: "The YEARFRAC function returns the fraction of a year represented by the number of whole days between two dates." – bjbk Jan 15 '16 at 0:34
  • The YEARFRAC function sounds like what I'm looking for. So this will return the number of days, for example July 1 to June 30 and then obviously divide that by 7 to return the weeks. In some formulas I need to use the number of weeks and fortnights in a year which vary with leap-years so I need a function like YEARFRAC by the sounds of it to work out the days and then weeks in the given or current year. – TimD Jan 15 '16 at 2:06
2

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

For example:

DATEDIF formula

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .