time format calculations

[shaunkaz]

Hi

Can someone please provide me with a fomula to calculate time

Total hours Worked Scheduled Hours Actual Time
07:00 08:00 -01:00

What I want is too calculate my flexi time if I work 9hrs then my actual would be 1hr ie(1 hr over Scheduled hrs) but if I were to work 7Hrs what I want is in the actual column to say -1Hrs under scheduled hrs.

I can get the time to display (- hrs)

Thanks

Shaun

This also has to be displayed in HH:MM format

 

[shaunkaz]

Just as an after thought, the answer will be used in a calculation as Im working out the accumative

 

[grizle]

As you've no doubt discovered Excel does not allow negaive time. Strictly speaking your spreadsheet is actually recording a period of time rather than a time: 7 hours rather than 7 o'clock, but I can see the advantage of data entry in this format.
　
So to get rid of the format is simple but not elegant:
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Total hours Worked</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Scheduled Hours</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Actual Time</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">07:00</TD><TD style="TEXT-ALIGN: center">08:00</TD><TD style="TEXT-ALIGN: right">0.291666667</TD><TD style="TEXT-ALIGN: right">0.333333333</TD><TD style="TEXT-ALIGN: right">-0.041666667</TD><TD style="TEXT-ALIGN: center">-01:00 hrs under scheduled hours</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">07:15</TD><TD style="TEXT-ALIGN: center">08:00</TD><TD style="TEXT-ALIGN: right">0.302083333</TD><TD style="TEXT-ALIGN: right">0.333333333</TD><TD style="TEXT-ALIGN: right">-0.03125</TD><TD style="TEXT-ALIGN: center">-00:45 hrs under scheduled hours</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">10:00</TD><TD style="TEXT-ALIGN: center">08:00</TD><TD style="TEXT-ALIGN: right">0.416666667</TD><TD style="TEXT-ALIGN: right">0.333333333</TD><TD style="TEXT-ALIGN: right">0.083333333</TD><TD style="TEXT-ALIGN: center">02:00 hrs over scheduled hours</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0.010416667</TD><TD style="TEXT-ALIGN: center">00:15 hrs over scheduled hours</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=A2</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=B2</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">=C2-D2</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">=IF(E2<0,"-"&TEXT(ABS(E2),"hh:mm")&" hrs under scheduled hours",TEXT(E2,"hh:mm")&" hrs over scheduled hours")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E6</TH><TD style="TEXT-ALIGN: left">=SUM(E2:E4)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F6</TH><TD style="TEXT-ALIGN: left">=IF(E6<0,"-"&TEXT(ABS(E6),"hh:mm")&" hrs under scheduled hours",TEXT(E6,"hh:mm")&" hrs over scheduled hours")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
I.e convert the times to general format and do the maths before converting back to HH:MM format. Of course the calculation columns can be hidden.

Its up to you of course but -ve time with under scheduled hours is a double negative