formula nearly working

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
[COLOR=#333333]=IF(TEXT('SF Export'!C2,"mm/yyyy")='Merg Sheet'!AK8,"New","Update")[/COLOR]
 

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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 ColumnFormula ResultCalculation FieldDesired Result
UpdateJan-18New
UpdateNew
UpdateNew
UpdateNew
UpdateNew
UpdateNew
01/01/2018UpdateNew
02/01/2018UpdateNew
03/01/2018UpdateNew
04/01/2018UpdateNew
05/01/2018UpdateNew
06/01/2018UpdateNew
12/08/2016NewUpdate
12/08/2016NewUpdate
27/03/2015NewUpdate
20/03/2015NewUpdate
31/12/2017NewUpdate
28/02/2017NewUpdate

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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"))
 
Last edited:

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,981
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top