Date Formula

fairchance

fairchance

New member
Joined
Jan 4, 2015
Messages
48
Reaction score
0
Points
0
Dear Sir,

I am seeking data formula/function that could calculate the number of days from the expected date to actual date.
I can come up with formula to calculate the number of dates between real dates and expected dates. Here is the example parameters:

Range Definition:(days of month)
Beg 1-10
mid 11-20
End 21-30

Expacted date for submission Actual Date Formula
21-Dec-1421-Dec-140
21-Dec-1418-Dec-14-3
21-Dec-1427-Dec-146
Beg Dec 14
9-Dec-14
 #VALUE!
End Dec 1418-Dec-14 #VALUE!
Mid Dec 1427-Dec-14 #VALUE!

How can i calculate date difference in case of date relate to particular range? Any one can help please.

Regards

Shehbaz
 
What should the result for those 3 be then?
 
I want a formula to calculate the difference between real data and expected date. what will be the formula in such particular case when date is based on some range assumption?
 
I understand, but each range has about 10 days... do you want to subtract the bottom of the range, the top of the range, which date to use?
 
This is the actual question what i want i.e. top,bottom,average or other date value which will be put in such formula?
 
Show what your expected results should be in those 3 cells.
 
I would need to calculate all possible ranges depending on the actual date, as it will differ in most of the cases.
That is, if for example, I have expected date is Mid Feb and actual date is 23 Feb, it should come out positive value of +3, as the range value for Mid is 11 to 20
- for example If expected date is Mid Feb and actual date is 15 feb, the value should be 0, as it was within the range of Mid
- Expected date is Mid Feb and the actual date is 09 Feb, the value should be -2, as range starts at 11.

I hope you will be clear now now give me the formula

Regards
 
I think this takes far too much effort. What happens when a month has 31 days or 28/29, using a default end of 30 will return a wrong result.
 
Correct what, I said it is too complex because months don't all have 31 days. What happens in Feb, or Sep, Apr, Jun and Nov?
 
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
smile.gif


Kind Regards

Shehbaz H.
 
It is not a big challenge for every expert to come up with a single cell formula. The challenge is understanding your request and not getting answers to our questions.

Having said that, and assuming data starts in A2:B2 (and making an educated guess for your request), then in C2 try this formula:

=IF(ISNUMBER(A2),B2-A2,DAY(B2)-DAY(LOOKUP(LEFT(A2,3),{"Beg","End","Mid"},{0,21,11})))

copied down.
 
Last edited:
Dear Sir,

Your given formula is not working correctly as it does not fulfill all conditions mentioned above e.g. please check the below condition:

If B4 within Expected date then B4-A4=0
 
Here is a one-cell solution, but I still say that your logic is flawed

=IF(ISNUMBER($A1),$B1-$A1,
IF(DATEVALUE(VLOOKUP(LEFT($A1,3),{"Beg",1;"Mid",11;"End",21},2,FALSE)&RIGHT($A1,LEN($A1)-3))>=$B1,
$B1-DATEVALUE(VLOOKUP(LEFT($A1,3),{"Beg",1;"Mid",11;"End",21},2,FALSE)&RIGHT($A1,LEN($A1)-3)),
IF(DATEVALUE(VLOOKUP(LEFT($A1,3),{"Beg",10;"Mid",20;"End",31},2,FALSE)&RIGHT($A1,LEN($A1)-3))<=$A1,
$B1-DATEVALUE(VLOOKUP(LEFT($A1,3),{"Beg",10;"Mid",20;"End",31},2,FALSE)&RIGHT($A1,LEN($A1)-3)),0)))
 
The formula is still not showing correct result as these are wrong entries as per criteria discussed:


ExpactedRealResultCorrect
Beg Dec 149-Dec-14-10
End Dec 1418-Dec-14-3-13
Mid Dec 1427-Dec-1477

 
So, there is no top class expert to answer my question here at this forum??? It is my request to solve my problem. I have an alternate solutions but it is also near to the answer:

=IF(AND(LEFT(A2,3)="beg",DAY(B2)>10),B2-(10&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="mid",OR(DAY(B2)<11,DAY(B2)>20)),B2-(20&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="end",DAY(B2)<21),B2-(31&RIGHT(A2,7)),IF(LEFT(A2,3)="beg",B2-(10&RIGHT(A2,7)),IF(LEFT(A2,3)="mid",B2-(20&RIGHT(A2,7)),IF(LEFT(A2,3)="end",B2-(31&RIGHT(A2,7)),B2-A2))))))

 
Another attempt... in C2:

=IF(ISNUMBER(A2),B2-A2,IF(LEFT(A2,3)="Beg",IF(DAY(B2)<=10,0,DAY(B2)-10),IF(LEFT(A2,3)="Mid",IF(AND(DAY(B2)>=11,DAY(B2)<=20),0,IF(DAY(B2)<=11,DAY(B2)-11,20-DAY(B2))),IF(LEFT(A2,3)="End",IF(DAY(B2)>=21,0,DAY(B2)-DAY(EOMONTH(B2,0)))))))

copied down.
 
Please copy the data into excel sheet then paste your formula and my formula then compare the result and evaluate my given criteria:

21-Dec-1421-Dec-14
21-Dec-1418-Dec-14
21-Dec-1427-Dec-14
Beg Dec 149-Dec-14
End Dec 1418-Dec-14
Mid Dec 1427-Dec-14
5-Dec-143-Dec-14
5-Dec-143-Dec-14
17-Nov-1410-Nov-14
Beg Jan 1522-Dec-14
Beg Jan 1515-Jan-15
5-Feb-154-Feb-15
29-Aug-1222-Aug-12
Mid Apr 144-Apr-13
Beg Oct 139-Oct-13
3-Feb-143-Feb-14
11-Apr-1410-Apr-14
End Sep 1424-Sep-14
Mid Sep 1424-Sep-14
End Oct 1430-Oct-14
17-Oct-1216-Oct-12
Beg Apr 134-Apr-13
Mid Oct 139-Oct-13
End Jan 143-Feb-14
End Sep24-Sep-14
Beg Sep 1430-Aug-12
Beg May 132-May-13
Mid Aug 1330-Aug-13
End Jan 1431-Jan-14
11-Apr-1410-Apr-14
End Oct 1431-Oct-14
End Jan 1431-Jan-14
End Dec 1324-Dec-13
11-Apr-149-Apr-14
Beg Aug 145-Aug-14
End Oct 145-Nov-14
End Mar 134-Apr-13
End Dec 139-Jan-14
 
You must log in or register to reply here.
Back
Top