Please help Excel gives 00-Jan-00

[santvik234]

Dear Excel Stalwarts,

My earlier colleague left, he was working on an excel sheet, which basically used to get the first business day of the month from the available data set, now it is giving an 00-Jan-00 error. ijust dont know what to do
=IF(A16<B$9,VLOOKUP(B$15,Summary!$AC$2:$AD$62,2,FALSE),IF(OR(A16=LOOKUP(B$8,Data!A:A,Data!A:A),A16=""),"",LOOKUP(B$8,Data!A:A,Data!A:A)))

Data: Has the data with dates and respective values
B$9 is the END date
B$8 is the start date
b$15 is the name of the product, which in summary (Summary!$AC$2:$AD$62,2,FALSE) searches the no of column of the respective product for its values

Please help

 

[etaf]

thats 0 using a date format
so change the format of the cell to General
and you will get zero

i dont know what results are being returned by that formula lookup
I suspect 0
a blank cell will return a zero with vlookup

 

[santvik234]

Hi, Thanks for your quick reply. There are no blank cells in it, if i manually put the end date as 30thjune 2024, then it does the calculation otherwise after pickingup the values of 3rd june ( which is the 1st working day of june), it is supposed to yield 30th june 2024, but instead if gives 00-Jan-00 error

Thanks

 

[santvik234]

thats 0 using a date format
so change the format of the cell to General
and you will get zero

i dont know what results are being returned by that formula lookup
I suspect 0
a blank cell will return a zero with vlookup

Hi, Thanks for your quick reply. There are no blank cells in it, if i manually put the end date as 30thjune 2024, then it does the calculation otherwise after pickingup the values of 3rd june ( which is the 1st working day of june), it is supposed to yield 30th june 2024, but instead if gives 00-Jan-00 error

Thanks

 

[etaf]

as i cannot see all your spreadsheet , i dont know where you are putting

if i manually put the end date as 30thjune 2024,

Or where you are getting the lookups from

you could share the file on a fileshare like dropbox or onedrive
BUT i suspect you have personal or cpmpany sensitive data in the spreadsheet and that would need to be blanked out - as this is a public forum available for anyone to see

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[santvik234]

as i cannot see all your spreadsheet , i dont know where you are putting

Or where you are getting the lookups from

you could share the file on a fileshare like dropbox or onedrive
BUT i suspect you have personal or cpmpany sensitive data in the spreadsheet and that would need to be blanked out - as this is a public forum available for anyone to see

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

thanks for your help. let me check if i can put an excel

 

[etaf]

ok, there is no need to quote ALL my post on each reply