Hi

I'm keeping track of actual hours worked v contracted hours, the total hours worked compared to the contracted hours. and the difference.

e.g. Monday - 07:00 start 15:00 finish - I have another column which calculates the number of hours worked.

Next to this column I have entered the contracted hours, e.g. 08:30 so for example the actual hours worked was 08:00 but the contracted hours are 08:30, how can I get the cell in the next column to show that they have worked 00:30 less than they should have? I'm just getting lots of hash marks.

Hoping there's a way around this.

Assuming that E1 is:-

Hours Worked Less Contracted Hours

 - and that cell E2 is where you are getting the hash marks change the formula in cell E2 to read:-

=(C2-D2)*24

 - then format that cell (cell E2) as:-

Number

You should now get the desired result.

With the default setting, Excel can't handle negative times.

Let's say actual hours worked is in C2 and contracted hours in D2.

The formula =C2-D2 will return #### if C2 is less than D2.

There are two workarounds.

1) You can change the formula to =24*(C2-D2) and format the cell with the formula as General or as Number with for example 2 decimal places. This version of the formula will return the time difference in decimal hours, e.g. -0.50 means minus half an hour (30 minutes), not minus 50 minutes.

- or -

2) Select File > Options, click Advanced and scroll down to the "When calculating this workbook" section.

Tick the check box "Use 1904 date system" and click OK.

Excel is now able to display negative times.

*** WARNING ***: all existing dates in the workbook will shift 4 years and 1 day forward, e.g. May 13, 2013 will become May 14, 2017.

The setting only affects this specific workbook, not others.

Thanks for that. I'd really like to keep the formatting as time but it isn't possible.

Thanks anyway.

Hi Hans

Thanks for that. I had a feeling there might be problems working with negative time (!!!)

Changing the date option won't work on this spreadsheet as I need to have accurate dates too.

Thanks anyway.

Viv

Thanks to all of you to share the helpful information to us, this will help the others who meet the same issue.

I solved the problem as following:

assume that 

E2 is starting time and F2 is finishing time ( format these cells in TIME formula , it doesn't matter 24 hr or Am/Pm )

then the formula will be 

=TEXT((24-E2)+(F2),"H::MM")

format of the cell should be ( NUMBER)

this formula will help you if the starting time will be at evening in D day and finishing time in morning in next day.