+ Reply to Thread
Results 1 to 17 of 17

find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

  1. #1
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Please help me again on the problem shown on the attached file.(My excel sheet is attached in the post)

    Thank you all very much in advance, I will give your all stars if you experts help give me the answers!



    1. (Urgent)

    Question: Formula to find remaining duration( in months or days unit) between two dates.
    Detail: the two dates, are both in 3 separate cells as Y/M/D, (instead of the date in the 1 cell) , e.g
    Today's Date: Y/M/D ( in 3 separate cells) : 2014(year)=A3, 2(month)=B3, 20(day)=C3
    Previous Date:Y/M/D ( in 3 separate cells) : 2011(year)=B10, 3(month)=C10, 4(day)= D10

    Answer:
    with the rule of 3 years membership( 2014-2011 is 3 year period);
    so to calculate the expiry clock(remaining duration) the remaining date for the two dates: is 0.39 in months, and 12 in days. but whats the excel formula for them?


    The excel sheet is attached in this post, you can insert the formula in the sheet or in the post here.


    2. ( Can reply later)
    Problem: An auto update formula for today's date (in Y/M/D , 3 cell format)
    Attached Files Attached Files
    Last edited by xcfeng95; 02-21-2014 at 11:23 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,213

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

    Without seeing your workbook, this is probably what you want...
    =IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Hi,

    Thank you for your reply and suggestion.


    I think the formula you gave me is incorrect, because for each date, it is separated into 3 columns, Y/M/D. (A1,B1,C1) instead of combine all together(2/20/2014) in one cell A1.

    PLEASE ALSO NOTE*( THE MEMBERSHIP LIMIT IS 3 YEARS, SO maybe there is a =3-(formula), to indicate that )

    I dont see any button that allow me to upload the workbook.




    Quote Originally Posted by FDibbins View Post
    Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

    Without seeing your workbook, this is probably what you want...
    =IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
    Last edited by xcfeng95; 02-20-2014 at 06:20 PM.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    It's hard to rush anything when you have to recreate the whole thing from scratch, but here is what I got:

    =DATEDIF(DATE($C$5,$D$5,$E$5),DATE($C$5,D7,E7),"ym") and copied down

    I'm not sure what you mean by decimal days. You don't have any hours included, so no difference would show a fraction of a day.

    Maybe you meant a fraction of a month? You could approximate a day:month fraction with this:

    =DATEDIF(DATE($C$5,$D$5,$E$5),DATE($C$5,D7,E7),"ym")+(DATEDIF(DATE($C$5,$D$5,$E$5),DATE($C$5,D7,E7),"md")*12/365.25)


    You can upload a sample by going to Go Advanced, scroll to the bottom, click manage attachments, and browse to your file and upload it.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,213

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    That formula is based on dates being in 1 cell (which is where a real date should be)

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  6. #6
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Hi, the file is uploaded in my post!

    can you review, and give me the correct formula! Thank you!



    Quote Originally Posted by FDibbins View Post
    That formula is based on dates being in 1 cell (which is where a real date should be)

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  7. #7
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Hi, the file is uploaded in my post! "Book1 xlsx"

    can you review, and give me the correct formula! Thank you!

  8. #8
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    I dont know why ??? the attachments are not able to access..
    Like my answer, then click * below

  9. #9
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    I am sorry, I was editing it, you can try to open it now!

    Quote Originally Posted by satabp View Post
    I dont know why ??? the attachments are not able to access..

  10. #10
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Please check the attachment
    Attached Files Attached Files

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    For days until expiry I think you can use this formula

    =DATE(B10+3,C10,D10)-DATE(A$3,B$3,C$3)

    That gives 11 for your example

    I don't know how you get 0.39, why that amount - what are the expected results for the other rows?
    Audere est facere

  12. #12
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    thank you,

    If with the rule of 3 years membership, (2011/3/4-2014/3/04, membership ends)and we count the days left from 2014/2/21 - 2014/3/4, it is 12 days left in the membership.

    And if i convert 12 days to months, its 0.39 months.(using an online calculator)

    why is it 11 days using your formula?



    Quote Originally Posted by daddylonglegs View Post
    For days until expiry I think you can use this formula

    =DATE(B10+3,C10,D10)-DATE(A$3,B$3,C$3)

    That gives 11 for your example

    I don't know how you get 0.39, why that amount - what are the expected results for the other rows?
    Last edited by xcfeng95; 02-21-2014 at 12:40 PM.

  13. #13
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Hi,

    thank you.

    If with the rule of 3 years membership, (membership period is 2011/3/4-2014/3/04)and if we count today's date (2014/2/21) - contract end date (2014/3/4), it is 12 days left in the membership.

    And if i convert 12 days to months, its 0.39 months.(using an online calculator)






    Quote Originally Posted by satabp View Post
    Please check the attachment

  14. #14
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Quote Originally Posted by xcfeng95 View Post
    And if i convert 12 days to months, its 0.39 months.(using an online calculator)
    Clearly months have a variable number of days so there is no "correct" way to convert days to a decimal number of months, it depends how you decide to do the calculation, you can treat a month as 30 days or use some other method

    Quote Originally Posted by xcfeng95 View Post
    why is it 11 days using your formula?
    How many days is it from today until tomorrow? - You might say one day, but if you count both today and tomorrow you would say 2, so presumably you are counting both today and the expiry date - in which case you can just use +1 on the end of my suggested formula.

  15. #15
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    can you also give me the formula in months

  16. #16
    Registered User
    Join Date
    02-14-2014
    Location
    ny
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    Yes, thats great, I added it.

    How can I convert this "day data" to months unit?

  17. #17
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

    I'm not sure what you want the results to be. If you divide 12 by 30 you'll get 0.4, so I'm assuming the divisor used is something like 365/12 so try

    =(DATE(B10+3,C10,D10)-DATE(A$3,B$3,C$3)+1)/365*12

+ 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: 15
    Last Post: 06-03-2014, 02:08 PM
  2. Date Formula for days of the month, new dates starting on the 2nd of the month.
    By Kenn Jerger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2013, 01:31 AM
  3. Replies: 2
    Last Post: 12-01-2012, 03:06 PM
  4. Drop days from dates to just month and year
    By mrrpv in forum Excel General
    Replies: 2
    Last Post: 02-01-2007, 12:49 PM
  5. Replies: 2
    Last Post: 07-26-2006, 08:20 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