Another Time Math Question

[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

 

[Bob Phillips]

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)

 

[David Payne]

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))))

 

[David Payne]

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))))

 

[Peo Sjoblom]

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

 

[JohnT]

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

 

[Peo Sjoblom]

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