Dear All
I am in a fix can any expert solve my problem
A B
Expacted Date Real Date
21-Dec-14 21-Dec-14
21-Dec-14 18-Dec-14
21-Dec-14 27-Dec-14
Beg Dec 14 9-Dec-14
End Dec 14 18-Dec-14
Mid Dec 14 27-Dec-14
I want to subtract number of days from real date to expected date B4-A4 based on the following criteria
Date Range for expected date
Beg 1-10
Mid 11-20
End 21-31
If B4 > Expected date range then B4-A4=B4-last range value
If B4 within Expected date then B4-A4=0
There are three conditions are in formula you know if,than,else
here is the else statement: =B4-IF(LEFT(A4,3)="Beg",10&RIGHT(A4,7),IF(LEFT(A4,3)="Mid",20&RIGHT(A4,7),IF(LEFT(A4,3)="End",31&RIGHT(A4,7),A4)))
Than statement is 0
I am in a problem for if statement which has three parts i.e.
1. IF(LEFT(A4,3)="Mid")
if(DAY(B4)>10 and DAY(B4)<21))
2. IF(LEFT(A4,3)="Beg")
if(DAY(B4)<=10)
2. IF(LEFT(A4,3)="End")
if(DAY(B4)<=31)
It is a big challenge for every expert here in this forum as i want this formula into single cell
Kind Regards
Shehbaz H.
Bookmarks