I`m trying to make a rather large Excel based personell managament tool
I`m using it to track what employees are needed where, who subs for whom, for how many hours as well as why. Finally all this data is to be used to auto-fill every employees timecard/timelist or whatever it`s called in english- the sheet showing hours worked that goes to "payment".
I`Ve identified the tasks needed to get there and i`m going through each of them. Two issues are giving me a massive headache, but for now i`ll restrict myself to my most current problem
Time calculations
In my excel sheet i type hours worked in the following format: 0830-1600
I need to convert that to time and then calculate the difference between the two. I wrote up a formula that does this exactly as I want, but only half the formula "auto-fills".
Here is an example:
<tbody>
</tbody>Formula used:
=MOD(RIGHT([@[Tidsrom:]];4)+TIME(13;10;0);1)-MOD(LEFT([@[Tidsrom:]];4)+TIME(8;30;0);1)
The problem is that only half the formula or function, updates when i drag/auto-fill. The only part that updates is the cell referance "@[Tidsrom:" (in english it means Timeframe). The second part of the formula doesn`t change- the TIME part remains the same and this means i need to manually change the numbers to complete the number to time conversion.
Would really appreciate some help with this.
Keep in mind:
Since I started in this particular job i spent the first 3 months building up the excel system i wanted. Took me that amount of time to figure out what information I needed to record and how i wanted it presented. I`ve standardized most of it and what i need are the various functions required to fully automate everything.
I could make things simpler by using more cells that would allow simpler functions, but that would NOT be backwards compatible with all the information i`ve already recorded. I don`t want to loose access to almost 4 months of data because I can`t figure this out.
I`m using it to track what employees are needed where, who subs for whom, for how many hours as well as why. Finally all this data is to be used to auto-fill every employees timecard/timelist or whatever it`s called in english- the sheet showing hours worked that goes to "payment".
I`Ve identified the tasks needed to get there and i`m going through each of them. Two issues are giving me a massive headache, but for now i`ll restrict myself to my most current problem
Time calculations
In my excel sheet i type hours worked in the following format: 0830-1600
I need to convert that to time and then calculate the difference between the two. I wrote up a formula that does this exactly as I want, but only half the formula "auto-fills".
Here is an example:
0830-1310 | 4:40 |
<tbody>
</tbody>
=MOD(RIGHT([@[Tidsrom:]];4)+TIME(13;10;0);1)-MOD(LEFT([@[Tidsrom:]];4)+TIME(8;30;0);1)
The problem is that only half the formula or function, updates when i drag/auto-fill. The only part that updates is the cell referance "@[Tidsrom:" (in english it means Timeframe). The second part of the formula doesn`t change- the TIME part remains the same and this means i need to manually change the numbers to complete the number to time conversion.
Would really appreciate some help with this.
Keep in mind:
Since I started in this particular job i spent the first 3 months building up the excel system i wanted. Took me that amount of time to figure out what information I needed to record and how i wanted it presented. I`ve standardized most of it and what i need are the various functions required to fully automate everything.
I could make things simpler by using more cells that would allow simpler functions, but that would NOT be backwards compatible with all the information i`ve already recorded. I don`t want to loose access to almost 4 months of data because I can`t figure this out.