# Help with Excel formula

Polopaul69™ 19:25 08 Jan 2008
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 2008

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 2008

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 2008

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

Polopaul69™ 19:59 08 Jan 2008

Nah widening columns makes no difference

VoG II 20:00 08 Jan 2008

What is the formula?

Polopaul69™ 20:01 08 Jan 2008

Example of formula: -

=SUM(P11:R11,N11)-O11

VoG II 20:05 08 Jan 2008

=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 2008

=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 2008

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 2008

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.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Converse's new logo: the trainer brand looks to its heritage for a fresh identity

Mac power user tips and hidden tricks

Comment lancer Windows 10 en mode sans échec ?