Nested function auto fill problem

[Baudolino]

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:

0830-1310

4:40

<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.

 

[gaz_chops]

Welcome to the forum.

Try

=TIME(MID(A1,6,2),RIGHT(A1,2),0)-TIME(LEFT(A1,2),MID(A1,3,2),0)

A1 is the cell containing 0830-1310

[/FONT][/COLOR][/FONT][TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]0830-1310[/TD]
  [TD="class: xl64, width: 65"][/TD]
  [TD="class: xl63, width: 65, align: right"]04:40[/TD]
 [/TR]
 [TR]
  [TD]0900-1715[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]08:15[/TD]
 [/TR]
 [TR]
  [TD]0800-1615[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]08:15[/TD]
 [/TR]
 [TR]
  [TD]0745-1945[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]12:00[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]
[FONT=Lucida Grande][COLOR=#333333][FONT=Verdana]

 

[Baudolino]

Welcome to the forum.

Try

=TIME(MID(A1,6,2),RIGHT(A1,2),0)-TIME(LEFT(A1,2),MID(A1,3,2),0)

A1 is the cell containing 0830-1310

[TABLE="width: 195"]
<tbody>[TR]
[TD="width: 65"]0830-1310[/TD]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl63, width: 65, align: right"]04:40[/TD]
[/TR]
[TR]
[TD]0900-1715[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]08:15[/TD]
[/TR]
[TR]
[TD]0800-1615[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]08:15[/TD]
[/TR]
[TR]
[TD]0745-1945[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]12:00[/TD]
[/TR]
</tbody>[/TABLE]

That was amazing.

Could you please walk me through why my formula failed and why yours works? I can see you made a more complex formula independent of numbers and going of the positions instead, but what is the significance of using ";" rather than "+" when nesting and why did you reverse the functions in the second half?
Why did you use the starting positions you used?

And thanks for the help. That was an elegant thing you did.

 

[gaz_chops]

I'm not sure what this part of your formula is "MOD(RIGHT([@[Tidsrom:]];4)"?

My formula recreates "TIME(13;10;0)-TIME(8;30;0)" using left, mid & right to obtain the Hrs & Mins.

 

[Baudolino]

=MOD(RIGHT([@[Tidsrom:]];4)+TIME(13;10;0);1)-MOD(LEFT([@[Tidsrom:]];4)+TIME(8;30;0);1)

My formula

RIGHT([@[Tidsrom:]];4) draws the 4 characters from the right in the cell referenced = 1310
TIME explains itself
MOD has to do with converting standard numbers to time

=MOD(RIGHT([@[Tidsrom:]];4)+TIME(13;10;0);1)
If i didn`t use MOD here, then the numbers wouldn`t get converted properly to TIME. I would only be able to convert "easy" time calculations. Using MOD allows me to convert any kind of time difference. The problem however was that the numbers in the TIME bracket were static and never updated during auto-fill.

This is the same limitation your function has. It can calculate simple time such as 0800-1300, but it can`t calculate 2000-0800. To do that you need to update the formula with MOD.

A combination of your formula and mine, can calculate any hours

=MOD(TIME(MID([@[Tidsrom:]];6;2);RIGHT([@[Tidsrom:]];2);0)-TIME(LEFT([@[Tidsrom:]];2);MID([@[Tidsrom:]];3;2);0);1)

This will auto convert any numbers to time and calculate the difference- even complex hours such as 2000-0800. This has to do with how excel understands time.

As to my questions though. What is the significance of nesting with ";" rather than "+" and why did you reverse the functions in the second half of the formula?

 

[gaz_chops]

It was the [@[Tidsrom:]] part that I didn't understand!

Your formula was adding 1310 to time(13;10;0), the 1310 would be treated as days in Excel, so 2000-0800 would = 1310.5 the mod function will remove the days and return the value after the decimal point, e.g. 0.5. So RIGHT([@[Tidsrom:]];4) is not actually doing anything.

The mod function is simply converting the negative value to a positive, you could just use ABS to remove the negative.
=ABS(TIME(MID(A1,6,2),RIGHT(A1,2),0)-TIME(LEFT(A1,2),MID(A1,3,2),0))

The 1st mid returns the Hrs from the end time, right returns the mins from the end time.
The Left returns the Hrs from the Start time and Mid returns the Mins from the start time.

 

[Baudolino]

[@[Tidsrom:]]= cell with the timeframe 0800-1310(tidsrom=timeframe).
RIGHT([@[Tidsrom:]];4) = the 4 characters from the right in the cell [@[Tidsrom:]] = 1310- it`s just in norwegian rather than english. Obviously it wasn`t up to the task though, since I couldn`t drag it anywhere.

One of the things I don`t understand is why you are nesting functions with ";" rather than "+". What is the significance there, if any? Does excel care? Are there specific reasons for using one or the other? I`m saying ";" where you are using "," because the excel i`m using requires ";".

ABS is clearly more efficient. Will use that instead- thanks.

This was a big headache sorted. Now I feel like i`m getting somewhere.

 

[gaz_chops]

Excel treats time as a fraction of a day, so 12 hours = .5, your formula was adding 1310 + 12 hours, the result in excel would be 1310.5, mod(1310.5,1) will remove the 1310 resulting in .5, hence that part of your formula didn't actually do anything.

The "," or ";" within the time function separates the hrs, mins & secs. so red = Hrs, green = mins. =TIME(MID(A1,6,2),RIGHT(A1,2),0)-TIME(LEFT(A1,2),MID(A1,3,2),0)

Not sure where you are referring to using a "+"?