dATE cALCULATION
 

Please help me€¦..

A B C D E F
Name DateON DateOff DateON DateOff Total Dys

The formula below is working fine but if the E is blank some four digit
numbers is showing.
Is there any way to solve this problem.
Actually I am trying to calculate the days worked between the dates in a
particular year. Some people work 12 time a year some 3 times.

=IF(B2="","",C2-B2+E2-D2+€¦€¦€¦€¦€¦..)

Thanks in advance

dATE cALCULATION
 

One way if there aren't too many cells to reference:

=IF(COUNT(B2:C2)=2,C2-B2,0)+IF(COUNT(D2:E2)=2,E2-D2,0)

--
Biff
Microsoft Excel MVP


"Udayan" wrote in message
...
Please help me...

A B C D E F
Name DateON DateOff DateON DateOff Total Dys

The formula below is working fine but if the E is blank some four digit
numbers is showing.
Is there any way to solve this problem.
Actually I am trying to calculate the days worked between the dates in a
particular year. Some people work 12 time a year some 3 times.

=IF(B2="","",C2-B2+E2-D2+.......)

Thanks in advance

dATE cALCULATION
 

Sir, I tried that but it is not adding each other.

=IF(COUNT(B2:C2)=2,C2-B2,0)
+IF(COUNT(D2:E2)=2,E2-D2,0)
+IF(COUNT(F2:G2)=2,G2-F2,0)
+IF(COUNT(H2:I2)=2,I2-H2,0)
+IF(COUNT(J2:K2)=2,K2-J2,0)
+IF(COUNT(L2:M2)=2,M2-L2,0)
+IF(COUNT(N2:O2)=2,O2-N2,0)
+IF(COUNT(P2:Q2)=2,Q2-P2,0)
+IF(COUNT(R2:S2)=2,S2-R2,0)
+IF(COUNT(T2:U2)=2,U2-T2,0)
+IF(COUNT(V2:W2)=2,W2-V2,0)

as per your guidence this was the formula I applied, is it correct?


"T. Valko" wrote:

One way if there aren't too many cells to reference:

=IF(COUNT(B2:C2)=2,C2-B2,0)+IF(COUNT(D2:E2)=2,E2-D2,0)

--
Biff
Microsoft Excel MVP


"Udayan" wrote in message
...
Please help me...

A B C D E F
Name DateON DateOff DateON DateOff Total Dys

The formula below is working fine but if the E is blank some four digit
numbers is showing.
Is there any way to solve this problem.
Actually I am trying to calculate the days worked between the dates in a
particular year. Some people work 12 time a year some 3 times.

=IF(B2="","",C2-B2+E2-D2+.......)

Thanks in advance

dATE cALCULATION
 

That'll work. It's syntactically correct and should work but it's kind of
long. This formula will do the same thing:

=SUMPRODUCT(--(MOD(COLUMN(C2:W2),2)=1),--(C2:W2<""),C2:W2-B2:V2)

--
Biff
Microsoft Excel MVP


"Udayan" wrote in message
...
Sir, I tried that but it is not adding each other.

=IF(COUNT(B2:C2)=2,C2-B2,0)
+IF(COUNT(D2:E2)=2,E2-D2,0)
+IF(COUNT(F2:G2)=2,G2-F2,0)
+IF(COUNT(H2:I2)=2,I2-H2,0)
+IF(COUNT(J2:K2)=2,K2-J2,0)
+IF(COUNT(L2:M2)=2,M2-L2,0)
+IF(COUNT(N2:O2)=2,O2-N2,0)
+IF(COUNT(P2:Q2)=2,Q2-P2,0)
+IF(COUNT(R2:S2)=2,S2-R2,0)
+IF(COUNT(T2:U2)=2,U2-T2,0)
+IF(COUNT(V2:W2)=2,W2-V2,0)

as per your guidence this was the formula I applied, is it correct?


"T. Valko" wrote:

One way if there aren't too many cells to reference:

=IF(COUNT(B2:C2)=2,C2-B2,0)+IF(COUNT(D2:E2)=2,E2-D2,0)

--
Biff
Microsoft Excel MVP


"Udayan" wrote in message
...
Please help me...

A B C D E F
Name DateON DateOff DateON DateOff Total Dys

The formula below is working fine but if the E is blank some four digit
numbers is showing.
Is there any way to solve this problem.
Actually I am trying to calculate the days worked between the dates in
a
particular year. Some people work 12 time a year some 3 times.

=IF(B2="","",C2-B2+E2-D2+.......)

Thanks in advance