formula nearly working

[Lorr81]

Hi,

I I have two sheets and In sheet 1 is a date field formatted as 01/01/2018 I need to compare this to a field in sheet where we will manually enter the month we are working in as 01/2018 and return "New" if the date is blank or equal to this date or "Update" if it was prior this date.

=IF(MONTH('SF Export'!C2)&YEAR('SF Export'!C2)=MONTH('Merg Sheet'!AK8)&YEAR('Merg Sheet'!AK8),"New","Update")

Thank You,

Lorraine

 

[Joe4]

Re: formula nearly working - Please help!

So what exactly is the issue?

If both your entries are date entries, it should work.
If one is a Text entry, it will not.

One way to tell is the following:
By default, date entries are right-justified and text entries are left-justified.

Another way:
Dates are actually stored as numbers in Excel. See what these return:
=ISNUMBER('SF Export'!C2)
=ISNUMBER('Merg Sheet'!AK8)
If either returns FALSE, you are dealing with Text and that is why your formula isn't working.

Let's say that your first is a date, but your second is Text (the "01/2018" one).
Then you could use:

[COLOR=#333333]=IF(TEXT('SF Export'!C2,"mm/yyyy")='Merg Sheet'!AK8,"New","Update")[/COLOR]

 

[Lorr81]

Re: formula nearly working - Please help!

Hi,
Thank you for the quick response please see below, both fields are dates but still no joy.

Source Column	Formula Result	Calculation Field	Desired Result
	Update	Jan-18	New
	Update		New
	Update		New
	Update		New
	Update		New
	Update		New
01/01/2018	Update		New
02/01/2018	Update		New
03/01/2018	Update		New
04/01/2018	Update		New
05/01/2018	Update		New
06/01/2018	Update		New
12/08/2016	New		Update
12/08/2016	New		Update
27/03/2015	New		Update
20/03/2015	New		Update
31/12/2017	New		Update
28/02/2017	New		Update

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

Re: formula nearly working - Please help!

I don't follow your data example, as it pertains to your original formula.
What is from the 'SF Export" sheet and what is from the 'Merg Sheet"?
What do you only have one data listed in your "Calculation Field"?
Are you comparing a whole column of dates to a single cell?
If so, you will want to use Absolute Referencing to lock that reference down in your formula so it doesn't move when you copy the formula down.

 

[Special-K99]

Re: formula nearly working - Please help!

Looking only at your results it almost looks like you have New and Update the wrong way round.

Why isn't the calculation field copied down the column like the Source date column?

Which is the SF Export data and which is Merge data?

Try

=IF(TEXT('Merg Sheet'!AK8="","New",IF(TEXT('SF Export'!C2,"yymm")<TEXT(''Merg Sheet'!AK8,"yymm"),"Update","New"))

 

[Lorr81]

Re: formula nearly working - Please help!

Hi,

Thank you all, the reason the calculation field is not copied down is because it is on the second sheet and the idea is that the user clicks 2 buttons on the second sheet that manipulates the date in sheet1 (SF Export) and pulls it to the relevant columns in sheet2 (Merge Sheet), As they will run this different months I have a field on sheet 2 that allows them to enter the month 01/2017.

the formula I am trying to create will them use this field to check all the dates in sheet1 (SF Export) and if they are blank or within the same month as the month entered in sheet2 (Merge Sheet) the result will be New, if they have date earlier than the date entered the result will be update, the purpose is to identify new records created this month versus existing records already created.

Thank You sorry if i am not being clear enough.

The current formula I have is this:

=IF(MONTH('SF Export'!N5)&YEAR('SF Export'!N5)>=MONTH('Merg Sheet'!$AW$8)&YEAR('Merg Sheet'!$AW$8),"Update","New")

However it is identifying blanks a new which is correct but also any date where the month has a one is also being marked as new, so 01,10,11.

I know I am probably doing something silly.