Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to calculate number of days between two dates(e.g. 05.05.2009. and 15.10.2009.) , but I don't know what formula(or function) to use. May anyone help me about this formula(or function), and give an example of using it(you can use dates what I wrote above)?

    Thank you in advance.

    Slaven
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='slaven_savic' post='786959' date='31-Jul-2009 11:16']I am trying to calculate number of days between two dates(e.g. 05.05.2009. and 15.10.2009.) , but I don't know what formula(or function) to use. May anyone help me about this formula(or function), and give an example of using it(you can use dates what I wrote above)?

    Thank you in advance.

    Slaven[/quote]
    If all you want is the number of days difference between those two dates then it is very easy.
    If the first date is in A1 and the second is in A2 then the difference is A2-A1, but remember to format the cell with the result as a number, so excel doesn't think it is a date.

    Alternatively you can use =datedif(A1,A2,"d"), you don't need to use datedif, but it has other options you might find useful, see this article for more info on the datedif function.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If the first date is in A1 and the second is in A2 then the difference is A2-A1, but remember to format the cell with the result as a number, so excel doesn't think it is a date.
    I addition to above, practically, we need to add 1 in formula to calculate actual diff.

    [attachment=84934:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='prasad' post='786966' date='31-Jul-2009 13:00']I addition to above, practically, we need to add 1 in formula to calculate actual diff.[/quote]
    That depends on how you define "difference"...
    I would say that the difference between today and tomorrow is 1 day, not 2 days...

  5. #5
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In this attachment sheet REALIZACIJA contains three columns that should be used for this operation.

    I need a formula that will give as a result number of work days(work week is from Mon to Fri), and there are no holidays in year. In cell R5 is end date, and in cell C5 is start date. Result should be in cell S5. Name of S column is "Valuta u danima".

    [quote name='StuartR' post='786961' date='31-Jul-2009 12:21']If all you want is the number of days difference between those two dates then it is very easy.
    If the first date is in A1 and the second is in A2 then the difference is A2-A1, but remember to format the cell with the result as a number, so excel doesn't think it is a date.

    Alternatively you can use =datedif(A1,A2,"d"), you don't need to use datedif, but it has other options you might find useful, see this article for more info on the datedif function.[/quote]
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='slaven_savic' post='786970' date='31-Jul-2009 12:16']I need a formula that will give as a result number of work days(work week is from Mon to Fri), and there are no holidays in year. In cell R5 is end date, and in cell C5 is start date. Result should be in cell S5. Name of S column is "Valuta u danima".[/quote]
    That is a completely different requirement. Try putting =networkdays(C5,R5,) in cell S5 and see if this gives the result you want. If you get an error then you may need to install or load the Analysis Toolpak.

    The third paramater to networkdays (which is blank in this example) can point to an array of holiday dates, if you want to exclude holidays.See this article for more information.

  7. #7
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to enter formula as you wrote, but I am getting #VALUE! error. I have installed Analysis Toolpak.

    May you download table(attachment) and make changes that are needed? I will be very thankful. This will so much help me, mostly because that this table is structure of project that is very urgent.

    Thank you anyway.

    [quote name='StuartR' post='786973' date='31-Jul-2009 13:21']That is a completely different requirement. Try putting =networkdays(C5,R5,) in cell S5 and see if this gives the result you want. If you get an error then you may need to install or load the Analysis Toolpak.

    The third paramater to networkdays (which is blank in this example) can point to an array of holiday dates, if you want to exclude holidays.See this article for more information.[/quote]

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='slaven_savic' post='786974' date='31-Jul-2009 17:14']I am trying to enter formula as you wrote, but I am getting #VALUE! error. I have installed Analysis Toolpak.

    May you download table(attachment) and make changes that are needed? I will be very thankful. This will so much help me, mostly because that this table is structure of project that is very urgent.

    Thank you anyway.[/quote]
    If you are getting error, quit all excel application and restart it again. The Analysis Toolpak will start working.
    Regards
    Prasad

  9. #9
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have done what you said, but I am getting same error(#VALUE!).

    Anyway, my Excel is ok.

    I really don't know what to do.

    Only solution is if anybody of people in this discussion is willing to download attachment, and enter formula(I work in excel for more than 10 years, but I have never had problem like this).

    Thank you anyway.

    [quote name='prasad' post='786975' date='31-Jul-2009 13:57']If you are getting error, quit all excel application and restart it again. The Analysis Toolpak will start working.[/quote]

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='slaven_savic' post='786976' date='31-Jul-2009 13:31']...
    I have done what you said, but I am getting same error(#VALUE!).[/quote]
    If you read the article on Networkdays that I pointed you to then you will see that...
    Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
    and that
    If any argument is not a valid date, NETWORKDAYS returns the #VALUE! error value.
    I suspect that you have invalid dates. Networkdays does work for me with manually entered dates, but maybe you are not entering them in the correct format for your system. Please post a copy of your workbook with the error, and let us know what regional settings you have for Windows.

  11. #11
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There is workbook with error.

    [quote name='StuartR' post='786977' date='31-Jul-2009 14:39']If you read the article on Networkdays that I pointed you to then you will see that...

    and that


    I suspect that you have invalid dates. Networkdays does work for me with manually entered dates, but maybe you are not entering them in the correct format for your system. Please post a copy of your workbook with the error, and let us know what regional settings you have for Windows.[/quote]
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Hello Slaven,

    The values in cells C5 and R5 are text values, not date values. Try the following:
    - Select cell C5.
    - Press the function key F2 to edit the cell, then press Enter without changing anything.
    - Do the same for cell R5.

    Do you see a valid number in cell S5 now?

  13. #13
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What format of date to use? Before I entered anything, I formatted cells as a date(you can see this by clicking Format cells command).

    I don't know why you are talking about cell F5(Kolicina). This cell is not cell that contains date, it contains number.

    [quote name='HansV' post='787043' date='31-Jul-2009 22:42']Hello Slaven,

    The values in cells C5 and R5 are text values, not date values. Try the following:
    - Select cell C5.
    - Press the function key F2 to edit the cell, then press Enter without changing anything.
    - Do the same for cell F5.

    Do you see a valid number in cell S5 now?[/quote]

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm sorry, cell F5 was a typing error, I meant cell R5.

    Were the data imported from another application? That could cause the cells C5 and R5 to be text even though you formatted them as dates.

  15. #15
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Data were not imported. I entered data manually.

    [quote name='HansV' post='787046' date='31-Jul-2009 23:04']I'm sorry, cell F5 was a typing error, I meant cell R5.

    Were the data imported from another application? That could cause the cells C5 and R5 to be text even though you formatted them as dates.[/quote]

Page 1 of 3 123 LastLast

Posting Permissions

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