Get Date prior to Actual Date

[mackypogi]

Hi

I am looking for a formula that could get the date prior to the actual date.
The formula like (Date - 1) wont work because it will only get the Date yesterday, what I mean is, I need to get the date prior to the actual date on a list of date to get the data I needed.

 

[AhoyNC]

Something like this?
This is an array formula and must be entered with

Excel Workbook
	A	B	C
1	1/2/2015
2	1/5/2015		2/9/2015
3	2/6/2015
4	2/9/2015
5	2/27/2015
6	3/1/2015
CTRL-SHIFT-ENTER.

 

[mackypogi]

Hi AhoyNC,

Yes something like that, actually that is the code I am using, something like below

'=(SUMIFS($J:$J,$C:$C,$C14,$D:$D,$D14,$A:$A,MAX(IF($C:$C=$C14,IF($D:$D=$D14,IF($A:$A<DATE(YEAR($A14),MONTH($A14),DAY($A14)),$A:$A)))))+SUMIFS($E:$E,$C:$C,$C14,$D:$D,$D14,$A:$A,MAX(IF($C:$C=$C14,IF($D:$D=$D14,IF($A:$A=DATE(YEAR($A14),MONTH($A14),DAY($A14)),$A:$A))))))-SUMIFS($F:$F,$C:$C,$C14,$D:$D,$D14,$A:$A,MAX(IF($C:$C=$C14,IF($D:$D=$D14,IF($A:$A<DATE(YEAR($A14),MONTH($A14),DAY($A14)),$A:$A)))))-SUMIFS($H:$H,$C:$C,$C14,$D:$D,$D14,$A:$A,MAX(IF($C:$C=$C14,IF($D:$D=$D14,IF($A:$A<DATE(YEAR($A14),MONTH($A14),DAY($A14)),$A:$A)))))

<DATE(YEAR($A14),MONTH($A14),DAY($A14)),$A:$a)))))+sumifs($e:$e,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a=date(year($a14),month($a14),day($a14)),$a:$a))))))-sumifs($f:$f,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))-sumifs($h:$h,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))<date(year($a14),month($a14),day($a14)),$a:$a)))))+sumifs($e:$e,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a=date(year($a14),month($a14),day($a14)),$a:$a))))))-sumifs($f:$f,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))-sumifs($h:$h,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))


The problem is, my excel is getting too slow with that array. do you think we can get an alternative with that formula?</DATE(YEAR($A14),MONTH($A14),DAY($A14)),$A:$a)))))+sumifs($e:$e,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a=date(year($a14),month($a14),day($a14)),$a:$a))))))-sumifs($f:$f,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))-sumifs($h:$h,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))<date(year($a14),month($a14),day($a14)),$a:$a)))))+sumifs($e:$e,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a=date(year($a14),month($a14),day($a14)),$a:$a))))))-sumifs($f:$f,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))-sumifs($h:$h,$c:$c,$c14,$d:$d,$d14,$a:$a,max(if($c:$c=$c14,if($d:$d=$d14,if($a:$a<date(year($a14),month($a14),day($a14)),$a:$a)))))

 

[AhoyNC]

One thing that might help is to not reference the whole column.
If your data only went down say 20,000 rows then use J1:J20000 vs J:J.

 

[mackypogi]

No other formula can make the same result using the Max If?

I was trying to upload a file, but I think only image can be uploaded here, can I email you the excel file? maybe you could help me.

btw I cross post this on

Get Date prior to actual Date

 

[AhoyNC]

Off hand I don't know of another formula (doesn't mean there isn't one) to get what you want.
You can post a small sample of your data by putting a border around some of the data and use copy / paste.
Someone else may have a VBA solution and as stated above referencing all rows in the columns can slow things down.

 

[S M C]

You didn't mention about the other cross-posts.

Get Date prior to Actual Date

 

[AhoyNC]

If your dates are in ascending order you might try using VLOOKUP with approximate match which should be faster than the array. If your dates are not in ascending order this will probably give you the wrong answer.

Excel Workbook
	A	B	C
1	1/2/2015
2	1/5/2015		2/9/2015
3	2/6/2015
4	2/9/2015
5	2/27/2015
6	3/1/2015
Sheet

 

[mackypogi]

Hi Ahoyc,

Thank you for your response, I will try and get back to you

 

[mackypogi]

Hi
I would like to re create my previous post,
I thought I can sort things out by just getting the formula for the Date prior to actual date, but now I really need help.
Ok, so here is my situation,
I need to get the Date Prior to Actual Date but it has multiple criteria.
I need a formula that is flexible so that I will just drag it down and it will calculate correctly.
I will attach the sample file I have so far, I also used the formula given by "Bebo"

the sample file is uploaded in other forum since I cannot upload the excel file here, sorry

I hope you guys out there could help me out. Thank you so much.
I also posted this question in below link: crosspost:
http://www.mrexcel.com/forum/excel-questions/838050-get-date-prior-actual-date.html
Get Date prior to actual Date
Date Prior to Actual Date
Get Date prior to Actual Date