Dates showing as 1 week ahead/behind

[sam94]

Hi Guys,

I'm using this formula to look up the date stored in one vile (shown as a number, e.g 35), and then pulling this back to the first sheet in order to convert it to a date. Problem is the date keeps showing up as a week after its supposed to be, anybody know the reason why?

=IF(VLOOKUP(B76,'[MAD File.xlsx]Feuil1'!$A:$K,11,0)="","",MAX(DATE(BN76,1,1),DATE(BN76,1,1)-WEEKDAY(DATE(BN76,1,1),2)+('[MAD File.xlsx]Feuil1'!K74)*7+1))


Thanks,

Sam

[sam94]

Ok I've now changed the formula to =IF(VLOOKUP(B69,'[MAD File.xlsx]Feuil1'!$A:$K,11,0)="","",MAX(DATE(BN69,1,1),DATE(BN69,1,1)-WEEKDAY(DATE(BN69,1,1),2)+((VLOOKUP(B69,'[MAD File.xlsx]Feuil1'!$A:$K,11,0))*7+1))) and seem to be getting the date to be showing 1 week ahead

[rajansettu]

guess it will work

Please Login or Register  to view this content.

[rajansettu]

if not please attache sample work sheet for referance..