Velocity Reviews > Xcell time/currency calculations.

# Xcell time/currency calculations.

Crash
Guest
Posts: n/a

 08-21-2007
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.

Lawrence D'Oliveiro
Guest
Posts: n/a

 08-21-2007
In message <(E-Mail Removed)>, 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?

Allistar
Guest
Posts: n/a

 08-21-2007
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.

Allistar
Guest
Posts: n/a

 08-21-2007
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.

Crash
Guest
Posts: n/a

 08-21-2007
On Tue, 21 Aug 2007 21:47:13 +1200, Lawrence D'Oliveiro
<(E-Mail Removed)_zealand> wrote:

>In message <(E-Mail Removed)>, 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.

Crash.

Crash
Guest
Posts: n/a

 08-21-2007
On Tue, 21 Aug 2007 21:59:01 +1200, Allistar <(E-Mail Removed)> 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.

Allistar
Guest
Posts: n/a

 08-21-2007
Crash wrote:

> On Tue, 21 Aug 2007 21:59:01 +1200, Allistar <(E-Mail Removed)> 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.

Allistar.

Robin Halligan
Guest
Posts: n/a

 08-21-2007
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?

At a quick glance your 6 is seen as a date

not sure how to fix that but you know what your looking for now

>
> TIA,
> Crash.

Alan
Guest
Posts: n/a

 08-21-2007
Hi,

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)

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:

http://www.velocityreviews.com/forums/(E-Mail Removed)

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

Lawrence D'Oliveiro
Guest
Posts: n/a

 08-21-2007
In message <(E-Mail Removed)>, 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.