If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|