Look up and add 1

[ads_1471]

Hi

After some help on a formula please which will make life so much easier.

What I am after is when a word is put into 1 column it will use this then to add 1 hour onto the hours on the same row.

Example below of table.

Basically when AL example is entered into Column F2 it then takes the hours from E2 and moved to G2 adding 1 hour on. then same with any entries below.

Date	Income	Time in	Time Out	Hours	Location	Total Hour + 1
2/8/22	Coach	11:00	13:00	2	AL Example	3
5/8/22	Coach	10:00	13:00	3	AL Example	4

 

[avicric]

Sorry, that didn't work. When hitting enter, it just came back with 2 hours, which was in the hour's field.

Basically anytime AL Example is in the CEL it should add 1 hour in the end column, If it has another word in then it should just be blank

Time In	Time Out	Total Hours	Location	hours used + 1
11:30​	12:30​	1​	AL Snooker	Should be 2
13:00​	15:00​	2​	Wales	Blank
13:00​	15:00​	2​	AL Snooker	Should be 3
13:00​	15:00​	2​	MAnchester	Blank
13:00​	15:00​	2​	Venue	Blank

Can you try without the space...
If(f2<>"",e2+1," ")

 

[Fluff]

How about

=IF(LEFT(F2,3)="AL ",E2+1,"")

 

[mumps]

Give the macro I suggested a try. Once you copy it into the sheet code module as I explained, you don't have to worry about extending the formula to other cells.

 

[avicric]

Can you try without the space...
If(f2<>"",e2+1," ")

Now i figure my bad
What you need is if the Word AL is in column Location then add 1 else blank