# Xcell time/currency calculations.

Discussion in 'NZ Computing' started by Crash, Aug 21, 2007.

1. ### CrashGuest

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.

Crash, Aug 21, 2007

2. ### Lawrence D'OliveiroGuest

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?

Lawrence D'Oliveiro, Aug 21, 2007

3. ### AllistarGuest

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, Aug 21, 2007
4. ### AllistarGuest

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.

Allistar, Aug 21, 2007
5. ### CrashGuest

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.

Crash.

Crash, Aug 21, 2007
6. ### CrashGuest

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.

Crash, Aug 21, 2007
7. ### AllistarGuest

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.

Allistar.

Allistar, Aug 21, 2007
8. ### Robin HalliganGuest

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.

Robin Halligan, Aug 21, 2007
9. ### AlanGuest

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:

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" <> 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.

Alan, Aug 21, 2007
10. ### Lawrence D'OliveiroGuest

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.

Lawrence D'Oliveiro, Aug 21, 2007
11. ### Jasen BettsGuest

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)

multiply by 24.

--

Bye.
Jasen

Jasen Betts, Sep 3, 2007
12. ### Jasen BettsGuest

On 2007-08-21, Alan <> wrote:
> Hi,
>
> Excel treats dates and times (essentially the same thing) as the
> number of days from 1 Jan 1900 (ignoring some legacy issues).

It's the number of days since 31 december 1899 (except before march 1900)

--

Bye.
Jasen

Jasen Betts, Sep 3, 2007