How to resolve this formula to value issue?

[Ksolo007]

I want to reference two different cell values into another formula but those values are returned using a formula.
In other words
The formula in
Cell F12 is =IF(C12<>"",NOW(),"") so lets say it returns a value of 5:50 PM
Cell G12 is the time manually inputted so lets say you input a value of 6:00 PM

if I want Cell K12 to show the amount of time elapsed - how do I write the formula?
also if I want to show an average time per task- lets say I performed 2 tasks in those 10 minutes
so if Cell H12 contains a manually inputted number "2" how do I
write a formula in Cell J12 to show average time per task.

 

[etaf]

Cell F12 is =IF(C12<>"",NOW(),"") so lets say it returns a value of 5:50 PM
Cell G12 is the time manually inputted so lets say you input a value of 6:00 PM

if I want Cell K12 to show the amount of time elapsed - how do I write the formula?

change the formula from
=IF(C12<>"",NOW(),"") to =IF(C12<>"",NOW()-today(),"")
That will remove the date part
Now you can in cell K12 enter G12-F12 , assuming the time in G12 is always greater than F12

so if Cell H12 contains a manually inputted number "2" how do I
write a formula in Cell J12 to show average time per task.

K2 / H12

Book1
	A	B	C	D	E	F	G	H	I	J	K
10
11						NOW	Entered Time	No. of task		Average Time	Time Diff
12			x			09:47:42	18:00:00	2		4:06	8:12
13
Sheet1

Cell Formulas
Range	Formula
F12	F12	=IF(C12<>"",NOW()-TODAY(),"")
J12	J12	=K12/2
K12	K12	=G12-F12

 

[joeu2004]

The formula in Cell F12 is =IF(C12<>"",NOW(),"") so lets say it returns a value of 5:50 PM

You might format it to display 5:50 PM. But NOW() returns date (an integer) as well as time (fraction of 24 hours).

So at a minimum, to extract time of day, the formula in F12 should be:

=IF(C12<>"", MOD(NOW(),1), "")

However, NOW() stores time with the precision of 0.01 seconds (multiple of 10 milliseconds). One way to truncate time [*] to the minute is:

=IF(C12<>"", --TEXT(NOW(), "h:m"), "")

formatted as Custom h:mm AM/PM.

The double-negate ("--") is one way to convert the text to a number.

[*] Excel rounds time to the second, then truncates the rounded time to the minute.

-----

Cell G12 is the time manually inputted so lets say you input a value of 6:00 PM
if I want Cell K12 to show the amount of time elapsed - how do I write the formula?

Assuming G12 is the later time, and F12 and G12 are within the same 24-hour period (but not necessarily on the same day), the formula in K12 should be:

=G12-F12+(G12<=F12)

formatted as Custom [h]:mm. The format spec [h] displays 24 (hours) if G12=F12.

The term G12<=F12 effectively adds 1 day (24 hours) in the case where midnight occurs between G12 and F12.

Note: It might be better to store current day and time as =NOW() in F12, and to include the date with time in G12. Then the elapsed time is simply =G12-F12.

-----

I want to show an average time per task- lets say I performed 2 tasks in those 10 minutes
so if Cell H12 contains a manually inputted number "2" how do I write a formula in Cell J12 to show average time per task

=K12 / H12

formatted as Custom [h]:mm:ss.000 if you want to display average time with millisecond precision.