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

Hi Marx,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Mark" wrote in message
...
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

"Mark" ha scritto nel messaggio


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)
----------------------------------------------------------------------------------------