Formula to populate a field with yes based on time value

[Paul15]

Hi I have a spreadsheet using a data input form with VBA in the background. on the form a user enters a 'start time' and 'finish time' with code working out the 'total time'. Now i need to be able to use this total time to either generate a power query if total time > than 01:00 (1hr), or set a field to 'yes' if the total time is > than 01:00. If we go the later way i can use the 'yes' in a query to generate the results I want.

i was thinking a simple =if(A1>01:00,"Yes","No") but this does not appear to work

any pointers please

Paul

 

[Dave Patton]

Review the alternatives below with Excel's Formula Evaluate.

Time.xlsm
	A	B	C	D	E
1	Start	End		Time	01:00
2	07:50	08:15	00:25	No	No
3	07:30	09:30	02:00	Yes	Yes
4
5a

Cell Formulas
Range	Formula
C2:C3	C2	=B2-A2
D2:D3	D2	=IF(C2>$E$1,"Yes","No")
E2	E2	=IF(C2>TIME(1,0,0),"Yes","No")
E3	E3	=IF(C3>TIME(1,,),"Yes","No")

 

[Paul15]

Review the alternatives below with Excel's Formula Evaluate.

Time.xlsm
	A	B	C	D	E
1	Start	End		Time	01:00
2	07:50	08:15	00:25	No	No
3	07:30	09:30	02:00	Yes	Yes
4
5a

Cell Formulas
Range	Formula
C2:C3	C2	=B2-A2
D2:D3	D2	=IF(C2>$E$1,"Yes","No")
E2	E2	=IF(C2>TIME(1,0,0),"Yes","No")
E3	E3	=IF(C3>TIME(1,,),"Yes","No")

Hi Dave,

that's great

I was working on something similar but did not pick up on putting my definite value 01:00 in a cell and working from that, so great. I have implemented the formula and it works.

I have copied the formula down to my last line of data and it picks up all those instances I want.

My hope is that for the next line of data entered through my Input form, the formula is automatically picked up on that line. I have multiple users in multiple area, all using the spreadsheet in SharePoint, so lots of data coming in. I don't want to have to drag the formula down the column to a million and one.

can the formula be set so it reflects the column it is in? my column AX, so as each new peice of data comes in the formula automatically is picked up?

Regards

Paul

 

[Dave Patton]

"can the formula be set so it reflects the column it is in? my column AX, so as each new peice of data comes in the formula automatically is picked up?"

The formula could increment down if it was part of a Table.

If the formula does not increment down, you can start a new thread.
Explain clearly what you require and post a concise example.