Help with Excel formula

  Polopaul69™ 19:25 08 Jan 08
Locked

I am creating a spreadsheet logging the times staff are present each day and i am trying to calculate a total number of hours worked formula and if on that day no hours are worked, it is showing as ##### instead of 0.

I know this might be not enough information for someone to assist me directly but does anyone know of where i can look to get help.

Many thanks

  VoG II 19:30 08 Jan 08

Are you subtracting times to get that result? Excel by default will not display negative times and puts in #####

There are two ways around this:

a) Use a formula like =MOD(A1-B1,1) which will display a positive time even if B1 is greater than A1.

b) In Tools > Options, switch to the 1904 date system.

  RobCharles1981 19:30 08 Jan 08

I should know this as I'm doing ECDL it looks to me that you might have to widen the colums??

  floor3013 19:37 08 Jan 08

i think you do need to move the columns bigger as then it can display the info.

  Polopaul69™ 19:59 08 Jan 08

Nah widening columns makes no difference

  VoG II 20:00 08 Jan 08

What is the formula?

  Polopaul69™ 20:01 08 Jan 08

Example of formula: -

=SUM(P11:R11,N11)-O11

  VoG II 20:05 08 Jan 08

=MOD(SUM(P11:R11,N11)-O11,1)

or

=IF(SUM(P11:R11,N11)<O11,0,SUM(P11:R11,N11)-O11)

or

change to the 1904 date system.

  Polopaul69™ 20:33 08 Jan 08

=IF(SUM(P11:R11,N11)<O11,0,SUM(P11:R11,N11)-O11)

Thats the puppy VoG - you are an absolute star ****

I unf am not authorised for everyone to change to the 1904 date system. You are always of help and inspiration to us all.

Many Thanks !!

  Polopaul69™ 20:37 08 Jan 08

One other thing, what formula do i use when inputting times throught the day so as i don't get #### when 2 times are needed to complete the formula

ie Start Time: 9:00
Finish Time

Hours Worked #####

  VoG II 20:40 08 Jan 08

With start time in A1 and finish time in B1

=IF(ISBLANK(B1),"",B1-A1)

the result cell will remain blank until a time is entered in B1.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now