Time formula
 

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

Perhaps this in c1

=ROUND(SUM(B1-A1)*96,0)/96

Mike

"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

a bit simpler

=ROUND((B1-A1)*96,0)/96

"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

Thanks Mike h This solves the first past of question but not second for as
soon as i carry out the calculation C1*3.90 i get the answer 0.28 and should
be 6.825.

"Mike H" wrote:

Perhaps this in c1

=ROUND(SUM(B1-A1)*96,0)/96

Mike

"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

Hi,

I should have explained it's all about formats. From the formula

=ROUND(SUM(B1-A1)*96,0)/96

You will get a time of 1:45 or 1 hr 45 minutes if you simply multiply this
by your hourly rate you will get the same answer each time but how you see
that answer depends on the format.

Formatted as a number or general you will see something like 0.28
Formatted as time you will see 6:49 6 hours 29 minutes
A point to mention is thst .28 of a day is 6 hrs and 49 minutes

So to get the correct answer you must multiply by 24
=(C1*3.9)*24

formatted as general you will get the answer you want of 6.825
I'll leave it to you to work out the relationship between 6hrs 49 munutes
and 6.825 but to point you in the correct direction they are the same number
expressed in different ways.

Mike


"jockj215" wrote:

Thanks Mike h This solves the first past of question but not second for as
soon as i carry out the calculation C1*3.90 i get the answer 0.28 and should
be 6.825.

"Mike H" wrote:

Perhaps this in c1

=ROUND(SUM(B1-A1)*96,0)/96

Mike

"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

=MROUND(B1-A1,TIME(,15,))*93.6

Format cell as General


"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

Thanks both seem to be good ways round problem, Teethless mama I thought
mround would work will need to get IT to add to my computer at work can i ask
where does 93.6 come from? and Mike H where does 96 come from? Me a bit thick
with these things so appreciate the help. as the £3.90 is the hourly cost and
is due for review so may change soon.

"Teethless mama" wrote:

=MROUND(B1-A1,TIME(,15,))*93.6

Format cell as General


"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

and Mike H where does 96 come from?

There are 96 quarter hours in a day

"jockj215" wrote:

Thanks both seem to be good ways round problem, Teethless mama I thought
mround would work will need to get IT to add to my computer at work can i ask
where does 93.6 come from? and Mike H where does 96 come from? Me a bit thick
with these things so appreciate the help. as the £3.90 is the hourly cost and
is due for review so may change soon.

"Teethless mama" wrote:

=MROUND(B1-A1,TIME(,15,))*93.6

Format cell as General


"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?

Time formula
 

That was just to obvious I should of seen that, Thank you.

"Mike H" wrote:

and Mike H where does 96 come from?

There are 96 quarter hours in a day

"jockj215" wrote:

Thanks both seem to be good ways round problem, Teethless mama I thought
mround would work will need to get IT to add to my computer at work can i ask
where does 93.6 come from? and Mike H where does 96 come from? Me a bit thick
with these things so appreciate the help. as the £3.90 is the hourly cost and
is due for review so may change soon.

"Teethless mama" wrote:

=MROUND(B1-A1,TIME(,15,))*93.6

Format cell as General


"jockj215" wrote:

Hi I have a small problem which should be easy to do but frustrating me just
a bit. I have some pool cars with different departments which need to be
charge for their use. I get weekly sheets in with start time and end time of
use. i.e. A1 08:45 B1 10:25 I need to calculate as follows C1 time car used
to nearest 15 minutes. D1 cost of use (C1*3.90). Should be easy but I think I
am making hard work of this and am sure there is a simple answer?