+ Reply to Thread
Results 1 to 12 of 12

calculate total number of days between two days based on 365 days year

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    Thiruvananthapuram
    MS-Off Ver
    Excel 2007
    Posts
    42

    calculate total number of days between two days based on 365 days year

    i need to calculate total number of days between two days in excel based on 365 days year.
    eg: start date: 01/02/1991
    end date: 31/01/2016
    Result: 9125 ( 25years*365 days)
    Any formula?

  2. #2
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: calculate total number of days between two days based on 365 days year

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

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,811

    Re: calculate total number of days between two days based on 365 days year

    Try

    =(YEAR(B1)-YEAR(A1))*365

    A1 = start year

    B1=End Year

  4. #4
    Registered User
    Join Date
    03-12-2013
    Location
    Thiruvananthapuram
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: calculate total number of days between two days based on 365 days year

    sir,
    this is not iam looking for. ineed actual number of days between two dates irrespective of years*365
    ie: end date-startdate=total number of days based on a 365 days year.
    01/02/1991 to 17/05/2001= ?

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: calculate total number of days between two days based on 365 days year

    Post SEVERAL examples and tell us what results you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,811

    Re: calculate total number of days between two days based on 365 days year

    Try

    =SUMPRODUCT((TEXT(ROW(INDIRECT(A1&":"&B1)),"ddmmm")<>"29Feb")+0)

    Found on this forum


    http://www.excelforum.com/excel-gene...eap-years.html

  7. #7
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: calculate total number of days between two days based on 365 days year

    To be fair, you did say
    25years*365 days
    and that is exactly what you have been given.

    In reality, you could just do
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    but that gets you 9130 because of all the Leap Years.

    So, the real question is, why do you want to get the number of days between two dates on the basis of a 365 day year when that will be an inaccurate calculation.

  8. #8
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: calculate total number of days between two days based on 365 days year

    @John: nice solution, credit to DLL ... though still don't understand the relevance of the calculation.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,811

    Re: calculate total number of days between two days based on 365 days year

    Do "Evaluate Formula" and you will set how it works: each date (list generated by ROW(INDIRECT...) is checked to see if it is 29th Feb. You get a list of TRUE/FALSE which is converted to 1/0 by the "+0". SUM of these gives the result.

    Very clever !!!

  10. #10
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: calculate total number of days between two days based on 365 days year

    Sorry John, you misunderstand me. I know how the formula works, just not why anyone would want the output from it. If you have two dates and want to know the number of days between them, I don't understand why you would ignore the Leap Days.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,811

    Re: calculate total number of days between two days based on 365 days year

    @InvisibleMan, Apologies but ..

    ... ours is not to reason why!!!
    Last edited by JohnTopley; 02-24-2016 at 10:10 AM.

  12. #12
    Registered User
    Join Date
    03-12-2013
    Location
    Thiruvananthapuram
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: calculate total number of days between two days based on 365 days year

    sir great this works and gives what i want thanks

+ 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. Replies: 0
    Last Post: 06-11-2015, 03:12 AM
  2. [SOLVED] How to calculate Year, Month and Day from total number of days?
    By Indra Rai in forum Excel General
    Replies: 4
    Last Post: 06-25-2014, 02:08 PM
  3. [SOLVED] Calculate the number days between two user input days.
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 10:44 PM
  4. Calculate total working days and excepted leave days
    By megaiooo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2013, 09:29 AM
  5. Calculate points for year based on last 180 days
    By Diogie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2013, 04:01 PM
  6. [SOLVED] Adding Total Number of Days Elapsed and Displaying >31 Days
    By cwwazy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2013, 08:18 AM
  7. Replies: 8
    Last Post: 05-10-2013, 05:37 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