Related:

- About dated if function
- Functions of spreadsheet - Articles
- Functionality of wifi - How-To - Connection
- What is mutator function in c++ - How-To - C++ Language
- Function key stuck on - Guide
- Acer function keys not working - How-To - Office Software

## 7 replies

Mazzaropi

- Posts
- 1950
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- January 9, 2022

**taghreed almuhajiri**, Good morning.

I believe there is nothing strange with the formula.

=DATEDIF(E4,F4,"d")....

**+**....(1)

This formulas does:

1st part: Difference between two dates with answer in days

2nd part: adds 1 to the result

**then**:

**a)**

**E4**= 03/01/2014 and

**F4**= 03/17/2014

=DATEDIF(E4,F4,"d")....

**+**....(1)

................

**16**................

**+**.....

**1**

--> =

**17**

**b)**

**E4**= 01/01/2014 and

**F4**= 01/01/2014 - Both same date

=DATEDIF(E4,F4,"d")....

**+**....(1)

................

**0**..................

**+**.....

**1**

--> =

**1**

**c)**

**E4**= Empty and

**F4**= Empty

...excel treats this empty as 0 (Zero)

=DATEDIF(E4,F4,"d")....

**+**....(1)

................

**0**..................

**+**.....

**1**

--> =

**1**

Conclusion: This formula works perfectly.

BUT...You said:

"

*...it shows me (1) in all the empty cells instead of (0) (that's ok)...*"

Then I can't understand your doubt.

Please, could you give us some real example for our better understanding about the problem?

I hope it helps.

Have a nice day.

taghreed almuhajiri

- Posts
- 13
- Registration date
- Saturday March 8, 2014
- Status
- Member
- Last seen
- March 19, 2014

Good morning,

this part like i said before is ok (to show 1 in all empty cells)

the problem here with the Total part

Ex :

E4 = 1-Jan and F4 = 5-Jan

G4 =5 -------------> =DATEDIF(E4,F4,"d")+(1)

I4 = 1-Feb and J4 = 5-Feb

K4 =5 --------------> =DATEDIF(I4,J4,"d")+(1)

M4 = 1-Mar and N4 = 5-Mar

O4 =5 --------------> =DATEDIF(M4,N4,"d")+(1)

lets say i have to count the total in Q4

its saying

it should be only 15 as you know

it counts 5 + 5 + 5 + (1) in the formula *3

i hope you can tell me how to fix it

thank you

this part like i said before is ok (to show 1 in all empty cells)

the problem here with the Total part

Ex :

E4 = 1-Jan and F4 = 5-Jan

G4 =5 -------------> =DATEDIF(E4,F4,"d")+(1)

I4 = 1-Feb and J4 = 5-Feb

K4 =5 --------------> =DATEDIF(I4,J4,"d")+(1)

M4 = 1-Mar and N4 = 5-Mar

O4 =5 --------------> =DATEDIF(M4,N4,"d")+(1)

lets say i have to count the total in Q4

**( G4+K4+O4)**its saying

**18**it should be only 15 as you know

it counts 5 + 5 + 5 + (1) in the formula *3

i hope you can tell me how to fix it

thank you

Mazzaropi

- Posts
- 1950
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- January 9, 2022

**taghreed almuhajiri**, Good morning.

E4 = 1-Jan and F4 = 5-Jan

--> G4 =5 -------------> =DATEDIF(E4,F4,"d")+(1)

I4 = 1-Feb and J4 = 5-Feb

--> K4 =5 --------------> =DATEDIF(I4,J4,"d")+(1)

M4 = 1-Mar and N4 = 5-Mar

--> O4 =5 --------------> =DATEDIF(M4,N4,"d")+(1)

All these formulas above are correct.

Remember you're using the (+1) part only on a DATADIF line formula.

The results:

**G4**=

**5**;

**K4**=

**5**;

**O4**=

**5**are

**OK**.

Then if you do:

**Q4**--> =(

**G4**+

**K4**+

**O4**)

**Q4**--> =(

**..5.**+

**.5.**+

**.5.**)

The result MUST be 15. Not 18.

Are you sure that you're using this formula above to obtain a result?

It sounds very strange.

Save your file at any free site and put a link here to download.

We need to see it to help you better.

taghreed almuhajiri

- Posts
- 13
- Registration date
- Saturday March 8, 2014
- Status
- Member
- Last seen
- March 19, 2014

i did copy your formulas just to make sure , and i found that when the 3 parts are full it shows 15 "correct"

BUT when i removed one of them its (11) as you see

http://www.megafileupload.com/en/file/510021/test-xlsx.html

is't normal "excel does that" !!!!

BUT when i removed one of them its (11) as you see

http://www.megafileupload.com/en/file/510021/test-xlsx.html

is't normal "excel does that" !!!!

Mazzaropi

- Posts
- 1950
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- January 9, 2022

**taghreed almuhajiri**, Good morning.

"

*...is't normal "excel does that" !!!!...*"

Sorry, but it's not normal.

Excel don't makes mistakes.

What happens is that our formulas, a lot of times, have a logic problem.

Then the result is not what we expect.

Now we saw your file the problem was solved.

You can control when a formula may work.

The formula was acting even with empty cells.

So always generated an error as you pointed at O4.

**Try to do**:

**Before**:

**G4**--> =DATEDIF(E4,F4,"d")+(1)

**Now...**:

**G4**--> =

**IF(AND(E4>0,F4>0),**DATEDIF(E4,F4,"d")+(1)

**,"")**

**Before**:

**K4**--> =DATEDIF(I4,J4,"d")+(1)

**Now...**:

**K4**--> =

**IF(AND(I4>0,J4>0),**DATEDIF(I4,J4,"d")+(1)

**,"")**

**Before**:

**O4**--> =DATEDIF(M4,N4,"d")+(1)

**Now...**:

**O4**--> =

**IF(AND(M4>0,N4>0),**DATEDIF(M4,N4,"d")+(1)

**,"")**

Please, tell us if it worked for you.

I hope it helps.

--

Belo Horizonte, Brasil.

Marcílio Lobão

taghreed almuhajiri

- Posts
- 13
- Registration date
- Saturday March 8, 2014
- Status
- Member
- Last seen
- March 19, 2014

God, this program is so complicated

anyway it worked, thanks again

appreciated

anyway it worked, thanks again

appreciated

- Posts
- 1950
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- January 9, 2022

**taghreed almuhajiri**, Good morning.

taghreed almuhajiri, not be discouraged.

Excel is not complicated.

Everything is a matter of understanding how the logic of data processing works.

Perhaps you are beginning to use Excel with the need to develop a complex task.

Then it gets a little harder, learn to use the program and at the same time develop a more complex product to work.

An excellent and free method to learn a lot about Excel formulas, always use the HELP program for each function.

He is very good and has real examples of how to make it work.

If you need, there will always be this forum with several people with great willingness to help.

Have a nice day.

--

Belo Horizonte, Brasil.

Marcílio Lobão