Calculate total cost from cost per minute 4

[ph2]

Does anyone know of an easy way to do the following calculation :

I have a value called "duration" in the format of HH:MM:SS
and I also have a value "Cost per minute" i.e 1.95

Please can anyone give me a formular to calculate the total cost (duration*Cost per minute).

Many thanks

 

[GlennUK]

Hi paulhenley,

formula is
=A1*24*60*B1

where duration is in A1 and cost per minute is in B1

Glenn.

 

[nevets2001uk]

Because the time is stored as one time format you will need to disect the various time elements from the time to correctly calculate the time. The formula which will do this and calculate the tota cost is:

=(HOUR(A1)*60*A2)+(MINUTE(A1)*A2)+(SECOND(A1)/60*A2)

Where A1 is the time in HH:MM:SS format and A2 is the cost per minute.

The first part takes the hours and multiplies by 60 to get minutes then multiplies by the cost per minute.
Added to this is the minutes multiplied by the cost per minute.
Finally the seconds are divided by 60 to get a minute value (i.e. 30 seconds / 60 is 0.5 minutes) This is again multilpied by the cost per minute.

This should carry out the task you need from your spreadsheet. Need anymore help with this, just post here, I have it on E-Mail Notify

 

[GlennUK]

Hi nevets2001uk,

there is no need to disect the various time elements. Time is stored as a serial number in Excel where a 24 period is represented by the value 1, and any fractions of a day as fractions of 1.

Therefore to convert a time contained in a cell, say A1, into minutes ( including partial minutes ), you simply multiply by 24 and 60. Multiplying by 24 converts the time to hours, and multiplying that result by 60 gives minutes.

As you can see by the difference in formulae, this can make calculations quite simple.

Hope that makes things clearer.

Glenn.

 

[ph2]

Thank you both for your help - I now have a far better understanding of the way Excel works.

I tried the formula than Glenn suggested and it worked first time - Many thanks Glenn.

The only problem I have now is that when I enter a duration date that is > 24 hours ie 42:19:19, Excel only displays it as 18:19:19.
I set up a custom format for the cell that now displays it as 01:18:19:19(dd:hh:mm:ss) This sort of works, but I would prefer it to show as 42 hours rather that 1 day & 18 Hours.

Thanks again for all your help.
Paul.

 

[GlennUK]

Hi Paul,

to have a duration longer than 24 hours displayed the way you want, use this number format ...

[hh]:mm

Glenn.

 

[ph2]

Hi Glenn,

You are an absolute star!

I've spent ages going through the help files and still couln't find the answer!

Many thanks for your help

Paul.

 

[Hasit]

Paul, if Glenn has helped you out, you may wish to award him a star, so that he knows his advice was useful (and worked) and other know that this particular posting was helpful. Others may then find Glenn's suggestion useful to them too.

 

[ph2]

Thank you Hasit for identifying this feature - I was not aware it existed.

I have awarded Glenn 2 votes for each of his excellent answers.

Many thanks

Paul

PS. I have also voted for your reply as I am sure that many users were not aware or do not use this feature.

 

[Zelandakh]

You can vote as often as you want, but only one star goes up. However, I didn't know about the square brackets trick either, so here's one from me