if cell is not blank, that run formula

[Trevor3007]

Morning etc,
I use the following formula,:-
=MOD(F33-E33,1)-D33

however if the retrospective cell is empty(f33), it displays ####, is there a formula that will not display #### ?

Thank you.

 

[fadee2]

try

=IF(F33="","",MOD(F33-E33,1)-D33)

If cell F33 is empty then the formula will return blank, otherwise will execute the MOD formula.

of

=IFERROR(MOD(F33-E33,1)-D33,"")

If your MOD formula evaluates to an error, IfError will return blank.

hth...

 

[Trevor3007]

try

=IF(F33="","",MOD(F33-E33,1)-D33)

If cell F33 is empty then the formula will return blank, otherwise will execute the MOD formula.

of

=IFERROR(MOD(F33-E33,1)-D33,"")

If your MOD formula evaluates to an error, IfError will return blank.

hth...

many thanks fadee2,
(and my apologies for giving you incorrect cell ref).

it works to fault... being when the applicable are not blank, the calculation is incorrect . I have to enter the times using 24hr format:-

e33 = start time = 18:00
f33 = end time= 02:00
d33 = break time = 0:30
c33 = hours worked = 7:30
using the =MOD(F33-E33,1)-D33 formula in c33, it returns the correct calculation of 7:30

but using the one you supplied ( amending to the correct cell ref) it returns 15:30. Please screenshot below. (note that d33 is a hidden column & why its not displayed below)

many thanks fadee2,
(and my apologies for giving you incorrect cell ref).

it works to fault... being when the applicable are not blank, the calculation is incorrect . I have to enter the times using 24hr format:-

e33 = start time = 18:00
f33 = end time= 02:00
d33 = break time = 0:30
c33 = hours worked = 7:30
using the =MOD(F33-E33,1)-D33 formula in c33, it returns the correct calculation of 7:30

but using the one you supplied ( amending to the correct cell ref) it returns 15:30. Please screenshot below.

View attachment 41960

Hoping you can sort? Thanks a bunch anyhoo & much appreciate your help.

 

[Fluff]

Both the formulae supplied by fadee2 work for me

+Fluff 1.xlsm
	B	C	D	E	F
33	07:30:00	07:30:00	00:30	18:00	02:00
Main

Cell Formulas
Range	Formula
B33	B33	=IFERROR(MOD(F33-E33,1)-D33,"")
C33	C33	=IF(F33="","",MOD(F33-E33,1)-D33)

 

[Trevor3007]

Both the formulae supplied by fadee2 work for me

+Fluff 1.xlsm
	B	C	D	E	F
33	07:30:00	07:30:00	00:30	18:00	02:00
Main

Cell Formulas
Range	Formula
B33	B33	=IFERROR(MOD(F33-E33,1)-D33,"")
C33	C33	=IF(F33="","",MOD(F33-E33,1)-D33)

Thanks Fluff ,
I think there was a prob with the actual spread sheet , which I added fadee2's into. I started with new spreadsheet & it worked ....but

I have been trying to sort myself but cannot. Can you assist?

many thanks Fluff.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also please update your profile to show what version of Excel you are using.

 

[Trevor3007]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also please update your profile to show what version of Excel you are using.

many thanks fluff,

time calc.xlsx
	A	B	C	D	E	F
1
2	Date	Day	Hours Worked	Start Time	Finish Time	Break
3
4	12-Jul	Monday	07:30	18:00	02:00	00:30
5	13-Jul	Tuesday	07:30	09:00	17:00	00:30
6	14-Jul	Wednesday	07:30	18:00	02:00	00:30
7	15-Jul	Thursday	07:30	09:00	17:00	00:30
8	16-Jul	Friday	07:30	18:00	02:00	00:30
9	17-Jul	Saturday	07:30	09:00	17:00	00:30
10	18-Jul	Sunday	07:30	18:00	02:00	00:30
11		TOTALS	2.19
12	Additional Hours Worked		-35.11
time calc

Cell Formulas
Range	Formula
B4:B10	B4	=TEXT(A4, "dddd")
A5:A10	A5	=SUM(A4)+1
C4:C10	C4	=IF(E4="","",MOD(E4-D4,1)-F4)
C11	C11	=SUM(C4:C10)
C12	C12	=SUM(C11)-37.3

 

[Fluff]

Ok, change the format of C11 to [h]:mm and in C12 use

=C11*24-37.3

 

[Trevor3007]

sorry for the late reply Fluff..


Perfect.. Thank you

 

[Trevor3007]

try

=IF(F33="","",MOD(F33-E33,1)-D33)

If cell F33 is empty then the formula will return blank, otherwise will execute the MOD formula.

of

=IFERROR(MOD(F33-E33,1)-D33,"")

If your MOD formula evaluates to an error, IfError will return blank.

hth...

Hi Fadee2,

sorry for the late reply. Just wanted thank you for your help.