1. 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)?

Slaven

2. [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)?

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. 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]

4. [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. 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]

6. [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. 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. [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.

9. 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. [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. 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]

12. 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. 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. 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. 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 Last

Posting Permissions

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