number of weeks between two dates

Please can you help find a formula to work out the number of weeks between two dates

I want to know if there is a 5 week gap or not between date now request and proposed works columns

the 5 week mark always starts on a monday e.g. if date requset was 30th July 25 then the five week mark would start on 4th august 25 - if this can be incorported it would be a bonus

@Paul Newcome Hi Paul, can you help with this request please if you are free. I would appreciate it.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Not necessarily. Remember… The formula is moving to the [Date NoW requested] to the next Monday if it is not already a Monday. So the formula is actually using 4 August 2025 as the "start date". That is 4 weeks and 2 days before 3 Sept.

All comments

Mike Wilday

To do this you would want to use:
=netdays([Date Now Requested]@row, [Proposed Works Start Date]@row)/7

This should give you the number of weeks.

Paul Newcome

I am assuming that if the original date is a Monday, you would use that date. Otherwise you would start at the following Monday. If that is correct, this should do the trick:

=([Proposed Works Start Date]@row - (([Date NoW requested]@row + (2 - WEEKDAY([Date NoW requested]@row))) + IF(WEEKDAY([Date NoW requested]@row) > 2, 7, 0))) / 7

The above will give you the number of weeks between the next Monday and the end date.

MazU

Hi @Paul Newcome , what if the date was not a monday, how can incorporate it

MazU

@Mike Wilday thank you Mike but im getting a decimal

Paul Newcome

@MazU The middle part of the formula takes care of that. If the date is a Monday, it uses that date. If it is not a Monday, it will grab the date from the next Monday and use that.

If you drop this part into a date type column, you should see what I mean:

=([Date NoW requested]@row + (2 - WEEKDAY([Date NoW requested]@row))) + IF(WEEKDAY([Date NoW requested]@row) > 2, 7, 0)

Paul Newcome

NOTE: My full formula could also potentially give decimals if the [Proposed Works Start Date] is also not a Monday. If you need to get rid of those, you can either hide them with column formatting, or use on of the ROUND/ROUNDUP/ROUND down functions.

Mike Wilday

https://community.smartsheet.com/discussion/comment/491172#Comment_491172

You can wrap that in a round, round up, or round down function…

=ROUNDDOWN(netdays([Date Now Requested]@row , [Proposed Works Start Date]@row )/7, 0)

OR if you want to not have a 0 you could do…
=IF(ROUNDDOWN(netdays([Date Now Requested]@row , [Proposed Works Start Date]@row )/7, 0)=0, "<1", ROUNDDOWN(netdays([Date Now Requested]@row , [Proposed Works Start Date]@row )/7, 0))

MazU

@Paul Newcome

i done one example here

29th july to 3rd sept

it is saying 4.2 weeks, should it not be more?

Paul Newcome

MazU

@Paul Newcome that makes totally sense.

I think the way the end user wants perceives is the week 3rd sept falls in, is the 5th week. So anything after that 4th week, i wanted to flag in another column as "ok" or if it's 4 weeks or less , then flag as error. Can i still do that with your formula?

Paul Newcome

Yes. This output is numerical in format, so you can use that in a comparison such as

=IF([# of Weeks]@row < 4, 1)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.