If Formula require to calculate No of Days

Closed
Learner - May 5, 2010 at 02:03 AM
rizvisa1
Posts
4479
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

please help,

Really appreciated

Regards,

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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"))))
0
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.

Please help

Regards
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
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.

Please advice,

Thanks & Regards,
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 5, 2010 at 08:26 AM
Again if you post the values, it will be easy to see what the issue is
0
yes, if I input 0 in B1 it shows the value like "0-JAN-1900" and if I input any other date it works fine.

Regards,
0