Calculating Flexi time

1Roberta1

Board Regular
Joined
Jul 27, 2007
Messages
230
Hi
I was hoping that someone could help me out, I have two questions about a spreadsheet where I am trying to calculate how much flexi time has been accured.

So far I have managed to do this:
Excel Workbook
ABCDEFGHI
2Training Department Flexi-TimeRegular Hours: 8
3NameStartLunch OutLunch InEndTotal HrsWorked HrsReg HrsTotal Flexi Accumilated
4Francis09:0012:0013:0018:009.008.001.00
5Joanne08:4512:3013:1516:307.757.000.75
6Alan09:1513:0014:1520:1511.009.751.25
7Karen22:0023:0000:3007:009.007.501.50
Sheet



The problem is that in cells f, g and h it is showing 15 mins as 25, 30 mins as 50 and 45 mins as 75.

So my first question is;
1)Is there something I can do with my formula that will make it show the correct length of time?

And my second question is;
2)Is there a formula that I can use to keep a running total of flexi in column I.

I should point out that although my sample shows all positive flexi times there will be occasions when the flexi time will be negative.

Thanks in advance for your time reading this post.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

First thing to do would be to switch to the 1904 date system which allows negative time values. Take care as this will effect any dates you have entered into the sheet.

Do you particularly want the results of your calculations to be displayed as decimals?

It would be easier not to mess about converting the values and just format the resulting cells as [hh]:mm. Putting the [] round the hh means that it will display time values more than 24 hours.

Dom
 

1Roberta1

Board Regular
Joined
Jul 27, 2007
Messages
230
:oops: I am sorry I should have said that I am a total noobie when it comes to excel :oops:

You have lost me :oops:

I can't find the 1904 date system you are talking about and when I format my cells as you describe I get big numbers

For example f4 now shows as 213:00 instead of the 8.00 as shown in my example.

Maybe the formula that I am trying to use in the first place is wrong.

I am using excel 2000 if that makes a difference.

:oops: Again I am sorry for not understanding :oops:
 

1Roberta1

Board Regular
Joined
Jul 27, 2007
Messages
230

ADVERTISEMENT

Ok I still don't know about the 1904, however after your second post I understand about the formula and it is working great.


Thanks so much, I really do appreciate your quick and helpful response :)
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

A bit of background:

Excel stores dates and time as what are known as serial dates. One day is equivalent to 1 and an hour is 1/24 = 0.041667.

If you enter either a date or a time into a cell and then format the cell as general you will see what I mean. E.g. 15/9/2007 will show as 37878 (being the number of days since 1/1/1900) and 13:30 as 0.5625 (0.41667 * 13.5). One drawback of the 1900 date system is that it doesn't allow negative values.

There is an alternative data system call the 1904 date system, selected through Tools...Options...Calculation which uses 1/1/1904 as the start date and does allow negatives. This lends it ideally to this sort of spreadsheet where negative time values are often required.

The reason why you are getting the huge values is because you are multiplying all your results by 24. This is unnecessary as you are able to just do calculations on time in the same way you would any other numeric value in Excel.

Hope it helps,

Dom
 

Charlie45

Board Regular
Joined
Dec 13, 2003
Messages
225

ADVERTISEMENT

If you remove the *24 from the end of your formulas then your time will display correctly in hours and minutes
 

1Roberta1

Board Regular
Joined
Jul 27, 2007
Messages
230
Great thanks for that.

Thing is I have changed my spreadsheet ever so slighty;
Excel Workbook
ABCDEFGHI
2Training Department Flexi-TimeRegular Hours: 8
3FrancisStartLunch OutLunch InEndTotal HrsWorked HrsFlexi HrsTotal Flexi Accumilated
414/09/200709:0012:0013:0018:0009:0008:0001:0001:00
515/09/200708:4512:3013:1516:3007:4507:0000:4501:45
616/09/200709:1513:0014:1520:1511:0009:4501:1503:00
717/09/200722:0023:0000:3007:0009:0007:3001:3004:30
818/09/200723:0000:0001:3008:0009:0007:3001:3006:00
919/09/200700:0000:0000:0000:0000:0000:0000:0006:00
1020/09/200708:0012:0014:0014:3006:3004:3002:0008:00
11
12
13Should beShould be
14-204:00
15
Sheet


As you can see i have decided that each employee should have their own sheet.

Everything is fine until i come to the negative flexi. We work 8 hours each day. So H10 should show -2hours as only 6 hours have been worked and in I10 it should show 04:00 as it should take away 2 hours from the 4 hours that is showing in I9.

Thanks to your explanation I have found 1904 (your explanation is great by the way) however this doesn't solve my problem.
:rolleyes:

What am i doing wrong?

So sorry to keep pestering you, but this is really starting to do my head in :(

Thanks for any advice you can give me.
 

1Roberta1

Board Regular
Joined
Jul 27, 2007
Messages
230
Solved

Ok I finally got it to work. I did as you said and changed to 1904 format then completed my spreadsheet as follows and everything is working out just fine, thanks for being patient with me :)
Excel Workbook
ABCDEFGHIJ
2Training Department Flexi-TimeRegular Hours: 8
3FrancisStartLunch OutLunch InEndTotal HrsWorked HrsRegular HoursFlexi HrsTotal Flexi Accumilated
41509:0012:0013:0018:0009:0008:0008:0000:0000:00
51608:4512:3013:1516:3007:4507:0008:00-01:00-01:00
61709:1513:0014:1520:1511:0009:4508:0001:4500:45
71822:0023:0000:3007:0009:0007:3008:00-00:3000:15
81923:0000:0001:3008:0009:0007:3008:00-00:30-00:15
92000:0000:0000:0000:0000:0000:0008:00-08:00-08:15
102108:0012:0014:0014:3006:3004:3008:00-03:30-11:45
112207:0012:0012:3019:3012:3012:0008:0004:00-07:45
122307:0012:0012:3019:3012:3012:0008:0004:00-03:45
132407:0012:0012:3019:3012:3012:0008:0004:0000:15
142507:0012:0012:3019:3012:3012:0008:0004:0004:15
152607:0012:0012:3019:3012:3012:0008:0004:0008:15
162707:0012:0012:3019:3012:3012:0008:0004:0012:15
172807:0012:0012:3019:3012:3012:0008:0004:0016:15
182907:0012:0012:3019:3012:3012:0008:0004:0020:15
193007:0012:0012:3019:3012:3012:0008:0004:0024:15
203107:0012:0012:3019:3012:3012:0008:0004:0028:15
Sheet


Thanks again for all your help.

If you feel up to it and you see a better way for this, please feel free to say, I am always interested in the different ways things can be done in excel :)
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,555
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top