Another Time Math Question

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I need to create the following a formula that calcs the %
of time the trading day is left. For example the trading
day = 6.5 hours using: 1:00pm - 6:30am * 24.

Now I need to take 1:00pm - now() / 6.5 giving me the
percent of the day left to trade.

I can not figure out the now() part and how to convert it
so the math works.

Thanks for the help!
Mike
 
Is it because NOW includes the date.

Haven't tried it, but try

=TIME(13,0,0)-(NOW()-TODAY())/6.5

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Well my solution is VERY complicated but I know that it works 100%. This took me a while to figure out and I got a little obsessed with finding an answer. I see that you may have a better soltuion from another post but here goes my solution

Setup the spreadsheet

A1 = 'Start Time

B1 = 'End Time

C1 = 'Current Time

D1 = '% Time Passed

E1 = '% Time Remaining

And here are the formulas

A2 = [Static Start Time] Reformat it to hh:mm:ss AM/PM (makes it easier to manipulate the time manually in the formula bar

B2 = [Static End Time] Copy formating from cell A

C2 = =NOW(

D2 = =(((IF((C2>=0.5),HOUR(C2+12),HOUR(C2))*3600)+(MINUTE(C2)*60)+(SECOND(C2)))-((IF((A2>=0.5),HOUR(A2+12),HOUR(A2))*3600)+(MINUTE(A2)*60)+(SECOND(A2))))/(((IF((B2>=0.5),HOUR(B2+12),HOUR(B2))*3600)+(MINUTE(B2)*60)+(SECOND(B2)))-((IF((A2>=0.5),HOUR(A2+12),HOUR(A2))*3600)+(MINUTE(A2)*60)+(SECOND(A2)))*((IF((A2>=0.5),HOUR(A2+12),HOUR(A2))*3600)+(MINUTE(A2)*60)+(SECOND(A2)))

E2 = =(((IF((B2>=0.5),HOUR(B2+12),HOUR(B2))*3600)+(MINUTE(B2)*60)+(SECOND(B2)))-((IF((C2>=0.5),HOUR(C2+12),HOUR(C2))*3600)+(MINUTE(C2)*60)+(SECOND(C2))))/(((IF((B2>=0.5),HOUR(B2+12),HOUR(B2))*3600)+(MINUTE(B2)*60)+(SECOND(B2)))-((IF((A2>=0.5),HOUR(A2+12),HOUR(A2))*3600)+(MINUTE(A2)*60)+(SECOND(A2))))
 
Correction

D2 = =(((IF((C2>=0.5),HOUR(C2+12),HOUR(C2))*3600)+(MINUTE(C2)*60)+(SECOND(C2)))-((IF((A2>=0.5),HOUR(A2+12),HOUR(A2))*3600)+(MINUTE(A2)*60)+(SECOND(A2))))/(((IF((B2>=0.5),HOUR(B2+12),HOUR(B2))*3600)+(MINUTE(B2)*60)+(SECOND(B2)))-((IF((A2>=0.5),HOUR(A2+12),HOUR(A2))*3600)+(MINUTE(A2)*60)+(SECOND(A2))))
 
Maybe something like this could work

=MIN((MOD(NOW(),1)-A1)/(B1-A1),1)

start time in A1 and end time in B1

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
That works well.

However, if the time is over the required amount it does not display a
a percentage higher than 100% (eg 130%)

Joh
 
That's because I put the MIN part there, I assumed that you only wanted to
count up to 100%. Take off the MIN

=(MOD(NOW(),1)-A1)/(B1-A1)


--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top