Hi



Wonder if you can help, I have attached the xls with sensitive data removed.

It has 3 tabs

Sheet 2 – which holds the data (and has links to the source based in Sharepoint)

PlannedMigrations

And

ActualMigrations

So the idea is if row on Sheet 2 has a date then it’s a plannedmigration and is should fall into the right cell on the plannedmigratiios tab in the right reporting period, if is migrated is TRUE then it should also show up on ActualMigrations within the right reporting period

I’m close but not close enough and can’t see what I’m doing wrong, help really appreciated.

Regards

John B

Attached Files

#Checklist - .xlsx (39.9 KB, 12 views)

Hi
There is nothing wrong with your formula that I can see. The issue is with your data. You are comparing "Apples" with "Pears" so to speak. So when you try and compare the two dates each date has the following format so will never ever result in a TRUE result:

One date is in the format 16/04/14 00:00 and the other date you are comparing with is in the same format but with a different time setting such as 16/04/14 11:00. So you see you will never get a TRUE match.

Hope this helps.
Tony

I haven't got XL right here.
However, based on OTPM's answer you can get rid of time by using tnhe INT function.
Something along the lines of =INT("16/04/14 00:00")=INT("16/04/14 11:00") might do the trick ( replace dates by necessary references of course)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post