Use time series in formula

[dlow]

Hi,

I have a total that I would like to disperse thru the day via the time.

Total sales yesterday were 2K the store was open from 8AM-4PM.

What we would like to know is: if its 11:50, what our sales were @ yesterday this time. [0.4870]
So I divide the total by the time I want to know H or M & that gives me a breakdown, but I am hung-up on how I can use the current time as my integer.

I know that my start time in Excel time is: 0.333333333 [8AM] Finish time is: 0.666666667 [4PM] & the range from 8-4 is 0.333333333.

I cant for the life of me tie this together. Any help would be greatly appreciated.

[Bernie Deitrick]

=(NOW()-TODAY()-"8:00")/("8:00")*B2

with the total sales in cell B2.

[dlow]

Thanks for the quick response Bernie.

I tried your formula & its close, but it gives me a value that is a little to big. I am making a sample workbook right now, I appreciate your help & dont want to waste your time.

[dlow]

Heres a sample workbook thx again for the help.
Sample Time.xlsx

[dlow]

It works fine I had 8-5 in my spread sheet instead of 8-4.

How would I modify this for a shorter time frame 4 hrs or an odd time 5.5 hrs?

[Bernie Deitrick]

The general formula is

=(NOW()-TODAY()-StartTime)/(FinishTime-Startime)*Value

Like so:

=(NOW()-TODAY()-"8:00")/("16:00"-"8:00")*B2

[dlow]

Thank a million I have been struggling for days on this. I already gave you a rep point & now I am gonna mark this solved.

[Bernie Deitrick]

Great - glad to hear it works the way you want.