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

 

[Phuoc]

Book1
	A	B	C	D	E	F	G
1	Date	Income	Time in	Time Out	Hours	Location	Total Hour + 1
2	2/8/2022	Coach	11:00	13:00		AL Example	3
3	5/8/2022	Coach	10:00	13:00		AL Example	4
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=IF(F2="",(D2-C2)*24,"")
G2:G3	G2	=IF(F2="","",(D2-C2)*24+1)

 

[ads_1471]

Thanks for this. With the F column it can contain other words. Should of said this part sorry. But from the formula just want it to add on 1 hour when AL Example is entered.

Would I enter =IF(F2="AL Snooker","",(D2-C2)*24+1)

 

[Phuoc]

Book1
	A	B	C	D	E	F	G
1	Date	Income	Time in	Time Out	Hours	Location	Total Hour + 1
2	2/8/2022	Coach	11:00	13:00	2	AL Snooker
3	5/8/2022	Coach	10:00	13:00		AL Example	4
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=IF(F2="AL Example","",(D2-C2)*24)
G2:G3	G2	=IF(E2<>"","",(D2-C2)*24+1)

 

[ads_1471]

When I do the first formula it then just leaves the hours blank

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter data in column F and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 6 Then Exit Sub
    Target.Offset(, 1) = Target.Offset(, -1) + 1
End Sub

 

[ads_1471]

Would rather just use a formula rather than macros. Thanks.

All I am after is a formula for Column G to look up when column F has AL Example in and take how many hours are in column E placing the result in Column G but adding 1 hour to the end result.

This to be for each row

 

[ads_1471]

Book1
	A	B	C	D	E	F	G
1	Date	Income	Time in	Time Out	Hours	Location	Total Hour + 1
2	2/8/2022	Coach	11:00	13:00	2	AL Snooker
3	5/8/2022	Coach	10:00	13:00		AL Example	4
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=IF(F2="AL Example","",(D2-C2)*24)
G2:G3	G2	=IF(E2<>"","",(D2-C2)*24+1)

Would rather just use a formula rather than macros. Thanks.

All I am after is a formula for Column G to look up when column F has AL Example in and take how many hours are in column E placing the result in Column G but adding 1 hour to the end result.

This to be for each row

 

[avicric]

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

Hi,
Can you try...
If(f2<>" ",e2+1," ")

 

[ads_1471]

Hi,
Can you try...
If(f2<>" ",e2+1," ")

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