Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Calculation (from entered date / 1yr later in next field) | Excel Worksheet Functions | |||
End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
Date Calculation | Excel Worksheet Functions | |||
date calculation | Excel Worksheet Functions |