+ Reply to Thread
Results 1 to 3 of 3

I need help with a formula calculating time

  1. #1
    Mark
    Guest

    I need help with a formula calculating time

    I have a simple spreadsheet calculating the hours worked by a person, In the
    columns i have start time, end time and hours worked. The columns are
    formatted into hh:mm. The hours worked are calculated by subtracting end
    time from start time, based on 24hour clock. This works fine if the hours are
    worked on the same day, but if the person starts at 18:00 and finishes at
    02:00 the subtraction doesnt work.

    If however i change the formatting of the cells to dd/mm/yyyy hh:mm this
    will work as the date helps. But i feel this would be too much info to enter
    for each person if i have over 100 people on the spreadsheet.

    Is there an easier way around this either by changing the original formula
    or maybe by entering the date once on a sperate cell and only entering the
    times in the rows for each person.

    Thanx
    Mark

  2. #2
    Niek Otten
    Guest

    Re: I need help with a formula calculating time

    Hi Marx,

    =IF(B1>A1,B1-A1,B1+1-A1)

    --

    Kind Regards,

    Niek Otten

    Microsoft MVP - Excel

    "Mark" <Mark@discussions.microsoft.com> wrote in message
    news:255E9394-4B5D-4B18-ACE1-3DA8E5838F74@microsoft.com...
    >I have a simple spreadsheet calculating the hours worked by a person, In
    >the
    > columns i have start time, end time and hours worked. The columns are
    > formatted into hh:mm. The hours worked are calculated by subtracting end
    > time from start time, based on 24hour clock. This works fine if the hours
    > are
    > worked on the same day, but if the person starts at 18:00 and finishes at
    > 02:00 the subtraction doesnt work.
    >
    > If however i change the formatting of the cells to dd/mm/yyyy hh:mm this
    > will work as the date helps. But i feel this would be too much info to
    > enter
    > for each person if i have over 100 people on the spreadsheet.
    >
    > Is there an easier way around this either by changing the original formula
    > or maybe by entering the date once on a sperate cell and only entering the
    > times in the rows for each person.
    >
    > Thanx
    > Mark




  3. #3
    Franz
    Guest

    Re: I need help with a formula calculating time

    "Mark" <Mark@discussions.microsoft.com>ha scritto nel messaggio
    255E9394-4B5D-4B18-ACE1-3DA8E5838F74@microsoft.com

    > I have a simple spreadsheet calculating the hours worked by a person,
    > In the columns i have start time, end time and hours worked. The
    > columns are formatted into hh:mm. The hours worked are calculated by
    > subtracting end time from start time, based on 24hour clock. This
    > works fine if the hours are worked on the same day, but if the person
    > starts at 18:00 and finishes at 02:00 the subtraction doesnt work.
    >
    > If however i change the formatting of the cells to dd/mm/yyyy hh:mm
    > this will work as the date helps. But i feel this would be too much
    > info to enter for each person if i have over 100 people on the
    > spreadsheet.
    >
    > Is there an easier way around this either by changing the original
    > formula or maybe by entering the date once on a sperate cell and only
    > entering the times in the rows for each person.
    >
    > Thanx
    > Mark


    Check out Chip Pearson's site
    http://www.cpearson.com/excel/overtime.htm
    http://www.cpearson.com/excel/DateTimeEntry.htm


    --
    Franz

    ----------------------------------------------------------------------------------------
    To reply translate from italian InVento (no capital letters)
    ----------------------------------------------------------------------------------------



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1