Xcell time/currency calculations.

I am using Excel from Office 2003 with XP PRO. I cant get time #
hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
-$1234.10' with 2 decimals. The receiving cell is the same format as
B26 and has the value of $16.25 (? - should be $390.00).

Advertisements

In message <>, Crash wrote:
> I am using Excel from Office 2003 with XP PRO. I cant get time #
> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
> -$1234.10' with 2 decimals. The receiving cell is the same format as
> B26 and has the value of $16.25 (? - should be $390.00).
>
> Can anyone identify what is wrong here?

Advertisements

Crash wrote:
> Greetings,
>
> I am using Excel from Office 2003 with XP PRO. I cant get time #
> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
> -$1234.10' with 2 decimals. The receiving cell is the same format as
> B26 and has the value of $16.25 (? - should be $390.00).
>
> Can anyone identify what is wrong here?

It seems that 6:00 is a quarter of 24 hours, and the calculation is treating
it as 0.25.

65 * 0.25 is 16.25 - the result you are seeing.

If it were me I'd specify the number of hours as a number and not a time.

Allistar wrote:
> Crash wrote:
>
>> Greetings,
>>
>> I am using Excel from Office 2003 with XP PRO. I cant get time #
>> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
>> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
>> -$1234.10' with 2 decimals. The receiving cell is the same format as
>> B26 and has the value of $16.25 (? - should be $390.00).
>>
>> Can anyone identify what is wrong here?
>
> It seems that 6:00 is a quarter of 24 hours, and the calculation is
> treating it as 0.25.
>
> 65 * 0.25 is 16.25 - the result you are seeing.
>
> If it were me I'd specify the number of hours as a number and not a time.
>
> Allistar.

On Tue, 21 Aug 2007 21:47:13 +1200, Lawrence D'Oliveiro
<_zealand> wrote:
>In message <>, Crash wrote:
>
>> I am using Excel from Office 2003 with XP PRO. I cant get time #
>> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
>> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
>> -$1234.10' with 2 decimals. The receiving cell is the same format as
>> B26 and has the value of $16.25 (? - should be $390.00).
>>
>> Can anyone identify what is wrong here?
>
>What are the actual (i.e. not formatted) values in the cells?

To the best of my knowledge those are the actual values - if there is
a way of viewing 'unformatted' values then I don't know it.

On Tue, 21 Aug 2007 21:59:01 +1200, Allistar <> wrote:
>Allistar wrote:
>
>> Crash wrote:
>>
>>> Greetings,
>>>
>>> I am using Excel from Office 2003 with XP PRO. I cant get time #
>>> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
>>> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
>>> -$1234.10' with 2 decimals. The receiving cell is the same format as
>>> B26 and has the value of $16.25 (? - should be $390.00).
>>>
>>> Can anyone identify what is wrong here?
>>
>> It seems that 6:00 is a quarter of 24 hours, and the calculation is
>> treating it as 0.25.
>>
>> 65 * 0.25 is 16.25 - the result you are seeing.
>>
>> If it were me I'd specify the number of hours as a number and not a time.
>>
>> Allistar.
>
>If I am right, you could change the formula to:
>
>=B25 * B26 * 24
>
>Allistar.

Brilliant - it works. Many thanks - I did not spot the relationship
between 65.00 and 16.25 (DOH!) - though why it considers 6:00 to be
0.25 eludes me ;-)

Crash wrote:
> On Tue, 21 Aug 2007 21:59:01 +1200, Allistar <> wrote:
>
>>Allistar wrote:
>>
>>> Crash wrote:
>>>
>>>> Greetings,
>>>>
>>>> I am using Excel from Office 2003 with XP PRO. I cant get time #
>>>> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
>>>> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
>>>> -$1234.10' with 2 decimals. The receiving cell is the same format as
>>>> B26 and has the value of $16.25 (? - should be $390.00).
>>>>
>>>> Can anyone identify what is wrong here?
>>>
>>> It seems that 6:00 is a quarter of 24 hours, and the calculation is
>>> treating it as 0.25.
>>>
>>> 65 * 0.25 is 16.25 - the result you are seeing.
>>>
>>> If it were me I'd specify the number of hours as a number and not a
>>> time.
>>>
>>> Allistar.
>>
>>If I am right, you could change the formula to:
>>
>>=B25 * B26 * 24
>>
>>Allistar.
>
> Brilliant - it works. Many thanks - I did not spot the relationship
> between 65.00 and 16.25 (DOH!) - though why it considers 6:00 to be
> 0.25 eludes me ;-)

No problem. Internally it probably represents a "time" as an Integer
proportional to 24 hours. If you enter "1" into a cell and then change the
format to a time, you'll most likely get 24:00.

On Tue, 21 Aug 2007 21:32:13 +1200, Crash wrote:
> Greetings,
>
> I am using Excel from Office 2003 with XP PRO. I cant get time #
> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
> -$1234.10' with 2 decimals. The receiving cell is the same format as
> B26 and has the value of $16.25 (? - should be $390.00).
> Can anyone identify what is wrong here?

Excel treats dates and times (essentially the same thing) as the
number of days from 1 Jan 1900 (ignoring some legacy issues).

When you enter a time, it converts that to the portion of a day. In
your case, 6 hrs = 0.25 of a day, so the actual *value* in the cell is
0.25 formatted as a time so display as 6:00hrs (say).

If you then multiply that cell by 65 you get 16.25.

Your problem then is really one of units. You are multiplying days by
an hourly rate. To fix, you have to convert them to be consistent:

Either convert the days to hours then multiply by an hourly rate:

(B25*24)*B26

Or convert the hourly rate to a daily rate, and apply that to the
days:

B25*(B26*24)

Same answer of course.

Read the excel help on date / time functions and it should all become
crystal clear!

HTH,

Alan.
--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

"Crash" <> wrote in message
news:...
> Greetings,
>
> I am using Excel from Office 2003 with XP PRO. I cant get time #
> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as
> 'Currency
> -$1234.10' with 2 decimals. The receiving cell is the same format
> as
> B26 and has the value of $16.25 (? - should be $390.00).
>
> Can anyone identify what is wrong here?
>
> TIA,
> Crash.

In message <>, Crash wrote:
> ... I did not spot the relationship
> between 65.00 and 16.25 (DOH!) - though why it considers 6:00 to be
> 0.25 eludes me ;-)

This is why you need to understand the difference between the actual values
in the cells, and the formatted displays that Excel is showing to you. If
you don't, then you are regularly going to encounter surprises like this.

On 2007-08-21, Crash <> wrote:
> On Tue, 21 Aug 2007 21:47:13 +1200, Lawrence D'Oliveiro
><_zealand> wrote:
>
>>In message <>, Crash wrote:
>>
>>> I am using Excel from Office 2003 with XP PRO. I cant get time #
>>> hourly rate to work. The formula is =B25*B26. B25 is 6:00 with the
>>> cell formatted as 'custom h:mm'. B26 is $65.00 formatted as 'Currency
>>> -$1234.10' with 2 decimals. The receiving cell is the same format as
>>> B26 and has the value of $16.25 (? - should be $390.00).
>>>
>>> Can anyone identify what is wrong here?
>>
>>What are the actual (i.e. not formatted) values in the cells?
>
> To the best of my knowledge those are the actual values - if there is
> a way of viewing 'unformatted' values then I don't know it.

time is measured (numerically) in days. (not hours, minutes, or seconds)

Share This Page

Welcome to Velocity Reviews!

Welcome to the Velocity Reviews, the place to come for the latest tech news and reviews.

Please join our friendly community by clicking the button below - it only takes a few seconds and is totally free. You'll be able to chat with other enthusiasts and get tech help from other members.
Sign up now!