Formula Based on Characters

[PGD15]

Hi.

got a question (hopefully quick) regarding formula based on characters within cells.

If i want to display a certain value if the first two characters in cell a2 are greater than the first two characters in a1 how would i do this?

I've tried (cells are different i am aware but same principle)

=IF(RIGHT(B45,2>RIGHT(B44,2)),C44+1,"1")


Any advice on how to tweak this?

 

[Jonmo1]

Pretty close, just a misplaced )

=IF(RIGHT(B45,2>RIGHT(B44,2)),C44+1,"1")
should be
=IF(RIGHT(B45,2)>RIGHT(B44,2),C44+1,"1")

But, you said the 'FIRST' 2 characters. You should be using the LEFT function then.
And the left function returns a text string, so testing for greater than might not work as you expect.
You may need to convert it to a real number with +0

Try
=IF(LEFT(B45,2)+0>LEFT(B44,2)+0,C44+1,"1")

 

[njimack]

=if(left(a2,2)+0>left(a1,2)+0,c1+1,1)

 

[PGD15]

thanks for the replies guys, I did mean left, yes was just beng a dumb

though the formula still doesn't work (i think its because of my data set) how do i attach a file i've created a dummy document to show what I mean as this is hard to describe

 

[Jonmo1]

You can't make attachments here.
Try using the HTML maker (see my signature for a link) to show us a sample of your sheet.

Or you can just manually type a few examples of the cell values.

 

[Joe4]

how do i attach a file i've created a dummy document to show

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Some people also try uploading files to file sharing sites like "DropBox", and providing a link. But just not that not everyone is able (or willing) to download files off of the internet, for security reasons.

 

[PGD15]

so cell b1 is in the date formatt 01/06/2018
b2 04/06/2018, b3 05/06/2018, etc downward(only weekdays though)
then cell c1 says says this is workday 1
I want the formula to say if the 4th is greater than the 1st (so b2>b1) display the number 2(previous work day + 1; see post) if its a new month the 01 will be less than 31st so if the formula isnt true display the value 1, meaning 1st workday of the month.

This is not to much work to do this mannually but im trying to automative this document so i don't have to do this daily.

 

[Jonmo1]

OK, if those are real dates, then the left function will not extract the 'day' value of that date..
Because dates are really just numbers incrementing by 1 since Jan 1 1900.
So 04/06/2018 (in dd/mm/yyyy format) is actually just the serial number 43255 (43 thousand 2 hundred fifty five days have passed since Jan 1 1900)
So LEFT(B2,2) = 43

Try
=IF(DAY(B45)>DAY(B44),C44+1,1)

 

[PGD15]

Ah I feel so dumb! this works perfectly and was so simply thank you!

 

[Jonmo1]

You're welcome.