none
How to Calculate Number of Weeks In excel refering to todays date in my System date.

    Question

  • I want to calculate Number of Weeks from Today in a Excel sheet. ex 13/07/2013 How many Weeks passed referring to today's date. Dose anyone know a formula or something for this?

    this is my date format in excel sheet and i have the same format in my system date. Please help

    13/07/2013
    07/03/2013
    07/09/2013
    07/03/2013


    prabhath

    Wednesday, July 17, 2013 3:02 AM

Answers

  • In your first post you stated your Windows and Excel formats were the same, and you gave an example of 13/07/2013 as a date (which would imply a date format of dd/mm/yyyy)

    In this response you indicate your date format is mm/dd/yyyy for both windows and excel.

    That being the case, the "13/07/2013" date is probably a text string, and not a "real date".

    The formula will work when applied to Excel Dates (the format does not matter).  The formula will not work when applied to text strings.

    Onceyou get your data straightened out, so that all of the dates are true Excel dates, the formula will work.

    If, in fact, your dates are all true dates, and not a mixture of text strings and dates, then there is some other problem with your workbook as there is no reason why that formula should not work.

    So please post back exactly what you mean by "It's not working mate"

    • error message:  if so what is the error
    • incorrect results:  if so, what is the original data, desired result, and actual result
    • Excel crash: any error messasge?
    • Other:  be descriptive

    One other possibility:  Ensure the cell containing your formula is formatted as General.


    Thursday, July 18, 2013 11:03 AM

All replies

  • If your "13/07/2013" is in A1, then put this in B1:

    =ROUNDDOWN((TODAY()-A1)/7,0)

    Doesn't that get you the number of weeks between today and the date in question?


    -jerrykern

    Wednesday, July 17, 2013 3:16 AM
  • it is not working mate. I am running Windows Xp and my windows date format is mm/dd/yyyy in excel also its same . but cant get it. is there any other ways of getting this thing done?

    prabhath

    Wednesday, July 17, 2013 9:15 AM
  • The date format shouldn't matter. Is the formula above throwing an error, or just not producing the value you want?

    -jerrykern

    Wednesday, July 17, 2013 11:57 AM
  • Hi,

    I agree with Jerry's formula, I think is should work.

    What's issue now for the formula?


    Jaynet Zhang
    TechNet Community Support

    Thursday, July 18, 2013 5:56 AM
    Moderator
  • In your first post you stated your Windows and Excel formats were the same, and you gave an example of 13/07/2013 as a date (which would imply a date format of dd/mm/yyyy)

    In this response you indicate your date format is mm/dd/yyyy for both windows and excel.

    That being the case, the "13/07/2013" date is probably a text string, and not a "real date".

    The formula will work when applied to Excel Dates (the format does not matter).  The formula will not work when applied to text strings.

    Onceyou get your data straightened out, so that all of the dates are true Excel dates, the formula will work.

    If, in fact, your dates are all true dates, and not a mixture of text strings and dates, then there is some other problem with your workbook as there is no reason why that formula should not work.

    So please post back exactly what you mean by "It's not working mate"

    • error message:  if so what is the error
    • incorrect results:  if so, what is the original data, desired result, and actual result
    • Excel crash: any error messasge?
    • Other:  be descriptive

    One other possibility:  Ensure the cell containing your formula is formatted as General.


    Thursday, July 18, 2013 11:03 AM