Xcell time/currency calculations.

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

  1. Crash

    Crash Guest

    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
    #1
    1. Advertising

  2. 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
    #2
    1. Advertising

  3. Crash

    Allistar Guest

    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
    #3
  4. Crash

    Allistar Guest

    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
    #4
  5. Crash

    Crash Guest

    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
    #5
  6. Crash

    Crash Guest

    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
    #6
  7. Crash

    Allistar Guest

    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
    #7
  8. 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
    #8
  9. Crash

    Alan Guest

    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)

    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.
    Alan, Aug 21, 2007
    #9
  10. 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
    #10
  11. Crash

    Jasen Betts Guest

    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
    #11
  12. Crash

    Jasen Betts Guest

    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
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. lisa g

    currency converter

    lisa g, Oct 8, 2003, in forum: Computer Support
    Replies:
    8
    Views:
    583
  2. Deepak

    cost price calculation for a multi-currency trade

    Deepak, Oct 22, 2003, in forum: Computer Information
    Replies:
    0
    Views:
    431
    Deepak
    Oct 22, 2003
  3. KnowledgeSeeker

    Least valuable currency

    KnowledgeSeeker, Mar 14, 2007, in forum: Computer Support
    Replies:
    30
    Views:
    5,973
    KnowledgeSeeker
    Mar 27, 2007
  4. hobby of collection of currency

    , Aug 22, 2007, in forum: Computer Support
    Replies:
    2
    Views:
    606
    WhzzKdd
    Aug 23, 2007
  5. Fritz Wuehler
    Replies:
    5
    Views:
    408
    Mike Yetto
    Mar 28, 2010
Loading...

Share This Page