How to resolve this formula to value issue?

Ksolo007

New Member
Joined
Jan 2, 2021
Messages
1
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,412
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJK
10
11NOWEntered TimeNo. of taskAverage TimeTime Diff
12x09:47:4218:00:0024:068:12
13
Sheet1
Cell Formulas
RangeFormula
F12F12=IF(C12<>"",NOW()-TODAY(),"")
J12J12=K12/2
K12K12=G12-F12
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top