Count values based on the current time

[Santhi28595]

Hi all,

I am trying to find a formula to count the values based on the entry time and current time.

I have put a time buckets to count the values of each person. The value should be returned is that based on the current time and entry time, we need to return the values in the summary.

Can anyone please help me out.

[kvsrinivasamurthy]

IN B7 then dragged across

Please Login or Register  to view this content.

[Santhi28595]

Hi thank you. But I am not seeing any values returning against each user.

[José Augusto]

Hi

You can try this other approach with a helper column

In H6 use this formula and drag down

Please Login or Register  to view this content.

In B6 use this formula and drag forward and down

Please Login or Register  to view this content.

See the file

[Santhi28595]

Hi Jose,

Thank you for the file. But i am really sorry I am unable to understand the logic u have done. Can u please explain me...

[Santhi28595]

Hi Thank you. But I don't see any values returning against the user names. Can u tell me what is the logic u have used.

[FDibbins]

I will take a look t your file, but note that TIME is a volatile function that only updates with a worksheet/file change.

[kvsrinivasamurthy]

Change the vales in B2:B6 suitably then you will see readings. Otherwise you will see all readings in last colmn as the difference in time is more than 7 Hrs as the day is also counted for calculation.

[Santhi28595]

Change the vales in B2:B6 suitably then you will see readings. Otherwise you will see all readings in last colmn as the difference in time is more than 7 Hrs as the day is also counted for calculation.

Hi- please refer attached. Even though I changed the values, there is no values returning against the names.

[Santhi28595]

Hi Jose,

I tried your file however I see values are getting returned. But what is the calculation that u used as =now()+4.2/24.

When I change the values in b2:b6 the values get disappeared in the main sheet. Can you please check and let me know.

[kvsrinivasamurthy]

Pl read my explanation in my previous post.

[Santhi28595]

Pl read my explanation in my previous post.

Hi I tried modifying the values but still the values did not populate. Please refer my attached Excel updated in the previous post. And let me know what is the disconnect.

[kvsrinivasamurthy]

To show that formula is working I have changed H2 value, fixed time given. Pl see file.

[Santhi28595]

To show that formula is working I have changed H2 value, fixed time given. Pl see file.

I think you have manually updated the base time. The base time should be considered as =now () which will result in the current time.

I changed the values for mathew and it is getting disappeared

[jason.b75]

Try this formula in B7 of the sample file from post #9 (or from post #13).

Please Login or Register  to view this content.

The results of this formula differ from kvsrinvivasamurthy's, hopefully one of us has it right.

[Santhi28595]

Hi Jason

Appreciate all your help. But I am sorry I m still not getting the values.

[jason.b75]

Have you looked at the file attached to post #13?

My suggestioon looked right with your original data, but with the changes that kvsrinvivasamurthy made to it to illustrate the problem you were encountering, I can see errors in my logic.

In my opinion, the file that kvsrinvivasamurthy attached to post #13 gives the 'correct' results based on what you have asked for. If it does not match what you expect, then you need to provide a better explanation, using fixed date and time entries in your sample to illustrate what is needed, not the NOW() function.

Remember, time changes constantly, adding that to the time zone differences between various users, a sample with =NOW() will always be wrong!

[Santhi28595]

Hi Jason

Agreed that I have used now function. But all I need is to calculate the values based on the current time. So I would get real time values.

[jason.b75]

That can be done after, but first you need to get it working with a fixed time as a control measure (again, see post #13).