+ Reply to Thread
Results 1 to 9 of 9

Date format

  1. #1
    Registered User
    Join Date
    05-06-2020
    Location
    Sydney
    MS-Off Ver
    10
    Posts
    14

    Date format

    Hi all

    Looking for a formula that allows a cell to display the Monday date through the year (by default), and updates itself. For example in week 43 of this year, Monday's date is 19/10. The cell needs to display this up to the time that 7 days have past, regardless of how many times the spreadsheet is open.
    This new date (eg 26/10) then needs to appear in another cell, in the same spreadsheet.

    Hope it makes sense!

    Thank you.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Date format

    If you start out with the 1st Monday of the year (6 Jan 2020) in cell C3, then you could use formulas in cells to the right of it as

    D3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill D3 right as far as needed, so probably into E3:BB3. Given them your preferred date number format.
    Last edited by hrlngrv; 10-22-2020 at 01:45 AM. Reason: generalized formula

  3. #3
    Registered User
    Join Date
    05-06-2020
    Location
    Sydney
    MS-Off Ver
    10
    Posts
    14

    Re: Date format

    Thanks but that doesn't seem to work? What I was after is a cell that displays this week (by date, in the year) and knows to update itself 7 days later..Thanks again.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Date format

    Quote Originally Posted by Aukvxa02 View Post
    Hi all
    This new date (eg 26/10) then needs to appear in another cell, in the same spreadsheet.
    So what happens to the cell with the old date? Does that become blank? Are these weekly dates progressing left to right across columns or vertically down the page?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    05-06-2020
    Location
    Sydney
    MS-Off Ver
    10
    Posts
    14

    Re: Date format

    See attachment, hopefully I've done a better job at explaining!

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Date format

    See the formulas in column F here.

    Basically, =SomeDate-WEEKDAY(SomeDate,3) will always return the date of the most recent Monday, which would be SomeDate itself it it's a Monday.

  7. #7
    Registered User
    Join Date
    05-06-2020
    Location
    Sydney
    MS-Off Ver
    10
    Posts
    14

    Re: Date format

    OK, thanks, so I now want to say Lookup today's date but return it as start of week, how do I do that PLEASE?

    Regards

  8. #8
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Date format

    Quote Originally Posted by Aukvxa02 View Post
    . . . Lookup today's date . . .
    Do you mean something other than TODAY() ? If you mean TODAY(),

    =TODAY()-WEEKDAY(TODAY(),3)

  9. #9
    Registered User
    Join Date
    05-06-2020
    Location
    Sydney
    MS-Off Ver
    10
    Posts
    14

    Re: Date format

    Exactly hrlngrv! THANK YOU!!

+ 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. Converting from the 1904 date format to the 1900 date format without losing data
    By Patty McJorst in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2020, 12:53 PM
  2. [SOLVED] Converting Date & Time (General format) to Date (Date format)
    By supremenuts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2017, 12:55 PM
  3. Replies: 4
    Last Post: 04-29-2015, 08:36 AM
  4. Date Format Of User PC Changes Display format date of Excel File after update
    By waihar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2014, 03:09 AM
  5. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  6. Need Formula/VBA Code to convert date in String Format to Normal Date format
    By Karthik Sen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2012, 04:54 AM
  7. Column Range with date Format MM/DD/YYYY independate of sys date Format
    By gaursh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2011, 03:31 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