Formula Help

[Redm4n83]

Hello

I have the following formula
=IF(AND($R3="",$AF3>="03:15"),$AF3-3/24,MEDIAN($R3,$AF3))

If the cels in the table are
R3 is blank
AF3 is "03:18

I would expect the answer to be "00:18"
For some silly reason it is returning an answer of "03:18"


I have set the formula up so that when R3 has a time entered into it, the formula would then find the half way mark of R3 and AF3,
I would also like the formula to only take the 3hours off of a time if it allows and does not go back into the previous day, hence the taking 3 hours off only if AF3 is greater then or equal to 03:15

Assistence would be appreciated please.

Cheers

 

[jtakw]

Hi,

Change the "03:15" part in your formula like:

Book1
	R	AF	AG
1			0:18
2
3		3:18
Sheet20

Cell Formulas
Range	Formula
AG1	=IF(AND($R3="",$AF3>=TIME(3,15,0)),$AF3-3/24,MEDIAN($R3,$AF3))

 

[Redm4n83]

Thank you jtakw.

Ive just tried this, and it gives a #REF ! error, not only to the cell that this formula is being entered into, but everything else that uses this cell as a reference.


I have no idea what to do next, hahaha.

 

[jtakw]

How did the 3:18 get in AF3, is it manually entered, or from a formula?
If it's from a formula, can you post the formula, please.
And how is AF3 formatted? What do you get with =ISNUMBER(AF3)

 

[Redm4n83]

Im not sure if there is a way of doing it, but if i was able to send the sheet it would make alot more sense, hehe.

So.. AF3 is formatted as a custom hh:mm
AF3 gets its input from another Cell, Formula =$G3
G3 is manually entered.

EDIT: I also tried the =ISNUMBER, It gives the same answer and my original format, "03:18"

Sometimes i wonder why i try with excel... i get soo lost,

 

[jtakw]

=ISNUMBER(AF3) will return either TRUE or FALSE, nothing else, so I don't know what you mean that it returns "03:18"
I suspect you have some values that are TEXT, and some that are NUMBERS.

Since you have values in cells that are taken from other cells, some manually entered, and maybe some from formulae, it's hard to figure out where the root of the problem is.
When you say G3 is manually entered, can you describe Exactly how you enter that time in the cell?

Also, look here for uploading attachments: https://www.mrexcel.com/forum/about-board/508133-attachments.html

You can upload your file to a FREE host sharing site like DropBox also.