Dividing Dates with numbers in a Spreadsheet

[nieironsio]

Hello

I can not think of a solution to my problem. The problem is i need to divide "number of calls" by "time logged on" (Helpdesk Scenario) - so i need to divide 55 calls by say 5 hours logged on:

I calculate the time logged on by subtracting an "=now()" function from a "shift started time" which is selected from a list.

the 55 calls exists as a manually entered value

any help would be appreciated.

Thanks

 

[Gavona]

you can subtract the date/time values and divide them.
=(now()-timeloggedon)/NumberOfCalls
Interpreting the answer will be your issue.
An answer of 1 would mean one day.
An answer of 0.5 would mean 12 hours (half a day).

Look at faq68-5245.

If still having problems post back with your formula.


Gavin

 

[xlbo]

Depends on what your calls ratio is compared to

You will need to convert your time into the appropriate time component i.e. to view calls per hour, you will need to convert your time to the number of physical hours:

Dates & Times in excel are based on 1 = 1 day

1 = 1 day
1 = 24 hours
1 = 1440 minutes
1 = 86400 seconds

so....

calls per day = Calls / (Time Logged On)
calls per hour = Calls / (Time Logged On / 24)
calls per minute = Calls / (Time Logged On / 1440)

Remember to format the result cells as numbers rather than date/time


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nieironsio]

hi - thanks for your responses

but i am still having the same problem - i want excel to calculate automatically through an equation the number of calls taken per hour but because xl figures 2:15 today as 39000 days since 1900 i can't divide number of calls taken by it.

i have:

=now - 01/01/1900 14:15 to get my time logged on giving me a value of approx 39000.

I then have a value of 55 - i can divide 39000 by 55 but it would be fairly useless other than an index.

thanks

 

[KenWright]

One way assuming they don't work more than 24 hours in a shift

=((NOW()-A1)-INT((NOW()-A1)))*24*60

or better still possibly

=MOD((NOW()-A1),1)*24*60

Seems there should be an easier way though.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[nieironsio]

Thanks Ken

I just managed to find the soluation (v similar to yours) on microsoft.com about 5 minutes before you posted

thanks though all the same

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

One thing to keep in mind:

The NOW() function is dynamic. That is, every time you update or open the sheet the function will recalculate for a new value or NOW.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

If you just want the hours with no day info, just use

=Now()-Today()

This will give you the fraction you require to not have to use Days



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question