+ Reply to Thread
Results 1 to 6 of 6

Starting Week number by specific time

  1. #1
    Registered User
    Join Date
    12-01-2014
    Location
    Sevenoaks
    MS-Off Ver
    2013
    Posts
    3

    Starting Week number by specific time

    Hi

    Is there a formula to work out date/time range so the week starts and ends on a specific time? I want to start a week from 6am and ends at 6am by week end.

    Thanks

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Starting Week number by specific time

    Can you provide a little more context? What intervals are your ranges (since you specified both date and time)? Do you have a sample workbook of how you hope for the results to look?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Starting Week number by specific time

    if you upload a sample it will most likely be easier to assist. sounds like you could simply subtract .25 from your date range in the weeknum formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where your date is in a2.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Starting Week number by specific time

    Subtracting the 0.25 is about as easy as it gets. (6 hours being .25 of a day)
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    12-01-2014
    Location
    Sevenoaks
    MS-Off Ver
    2013
    Posts
    3

    Re: Starting Week number by specific time

    Hi


    Thank you for the help so far.

    I have 2 columns A contains the date, B is the time and C is the Week Number. I would like to start a week where the time starts at 6am on a Sunday and finish the following Sunday at 6am.


    A B C
    14/09/2014 23:28:18 37
    14/09/2014 23:28:18 37
    14/09/2014 23:41:20 37
    14/09/2014 23:48:05 37
    15/09/2014 00:25:51 38
    15/09/2014 00:35:02 38
    15/09/2014 01:14:42 38
    15/09/2014 02:28:03 38


    I've used =WEEKNUM(A2-0.25,1) but unsure how to integrate the formula with column B as well.

    Thanks

  6. #6
    Registered User
    Join Date
    12-01-2014
    Location
    Sevenoaks
    MS-Off Ver
    2013
    Posts
    3

    Re: Starting Week number by specific time

    I have used the following formula which has worked. Thanks!!

    =WEEKNUM(A2+(B2-0.25),1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Format a cell to display week number starting from a certain date
    By avolkmar in forum Excel General
    Replies: 3
    Last Post: 09-23-2014, 02:34 PM
  2. Replies: 5
    Last Post: 09-01-2014, 03:55 PM
  3. Convert date to week number for fiscal year starting July 25
    By ARayburn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-15-2013, 10:07 AM
  4. Receiving a date week by week depends on specific number?
    By ExcelBG in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2013, 10:20 AM
  5. Replies: 0
    Last Post: 01-21-2013, 08:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1