If Formula require to calculate No of Days

Closed
Learner - May 5, 2010 at 02:03 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 5, 2010 at 08:49 AM
Hi,

I have four dates in my work sheet, among them 2 are fixed and two variables. The data is as follows:

Date of Purchase (DP) = variable
Date of Sale (DS) = variable
Period begin date (PBD)= 30-JUN-09
Period end date (PED)= 30-APR-10
Result (Number of Days)

Conditions are as follows:

if (DP and DS) is not null and (DP and DS) < PBD then Result= 0
if DP < PBD and DS is null then result = PED-PBD
if (DP and DS) > PBD then result = DS-DP
if DP > PBD and DS is null then Result = PED-DP

Really appreciated

Regards,

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 5, 2010 at 05:58 AM
LET SAY
DP is in cell A1
DS is in cell B1
PBD is in cell C1
PED is in cell D1

you can write in E1 this

=IF(AND(A1<> "", B1<>"", A1<C1, B1<C1), 0, IF(AND(A1<C1, ISBLANK(B1)), D1-C1, IF(AND(A1>C1, B1>C1), B1-A1, IF(AND(A1>C1, ISBLANK(B1)), D1-A1, "None of 4 conditions"))))
Thanks for the help but I have faced another problem if I leave the B1 cell empty the formula considers the value = '0-JAN-1900'

How can I resolve this thing.

Regards
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 5, 2010 at 08:00 AM
what are the values of a1, b1, c1, and d1 are you using when you get this issue
All are in date format but I leave the B1 empty if not required. B1 is the Date of Disposal so if the object is not disposed off the Cell B1 left empty.