In column J I have the following formula =IF(E7="";"";J5+(E7-F7)/24) and
everything works fine, but my problem is that if a 0 is in column E then the
status days in column J start to go backwards what I need is to add the
deference between column D and F to Column J if there is a 0 in column E but
the old formula needs to work if there is a number like 2,5 hours added to
column E.

Does anyone have any ideas on this???


D E F G H
I J

Est. Actual AFE Start 05-mar-06 11:30 Status
Hrs. Hrs Hrs Day Finished Days
Date Time

18,0 25,5 26,1 to 18-mai-06 07:30 16,0
3,0 1,0 2,5 to 18-mai-06 08:30 15,9
3,0 3,0 0,0 to 18-mai-06 11:30 16,0
18,0 17,5 0,0 fr 19-mai-06 05:00 16,8
18,0 0,0 0,0 fr 19-mai-06 23:00 16,8
4,0 0,0 2,5 lø 20-mai-06 03:00 16,7
105,0 0,0 63,8 on 24-mai-06 12:00 14,0
8,0 0,0 2,5 on 24-mai-06 20:00 13,9
2,5 0,0 9,8 on 24-mai-06 22:30 13,5
5,0 0,0 3,7 to 25-mai-06 03:30 13,3
2,0 0,0 9,8 to 25-mai-06 05:30 12,9
2,0 0,0 2,5 to 25-mai-06 07:30 12,8
4,0 0,0 7,4 to 25-mai-06 11:30 12,5

Try this:

=if(e7=0;j5+(d7-f7)/24;If(e7="";"";j5+(e7-j7)/24))

HTH

"need help in Norway" wrote:

In column J I have the following formula =IF(E7="";"";J5+(E7-F7)/24) and
everything works fine, but my problem is that if a 0 is in column E then the
status days in column J start to go backwards what I need is to add the
deference between column D and F to Column J if there is a 0 in column E but
the old formula needs to work if there is a number like 2,5 hours added to
column E.

Does anyone have any ideas on this???


D E F G H
I J

Est. Actual AFE Start 05-mar-06 11:30 Status
Hrs. Hrs Hrs Day Finished Days
Date Time

18,0 25,5 26,1 to 18-mai-06 07:30 16,0
3,0 1,0 2,5 to 18-mai-06 08:30 15,9
3,0 3,0 0,0 to 18-mai-06 11:30 16,0
18,0 17,5 0,0 fr 19-mai-06 05:00 16,8
18,0 0,0 0,0 fr 19-mai-06 23:00 16,8
4,0 0,0 2,5 lø 20-mai-06 03:00 16,7
105,0 0,0 63,8 on 24-mai-06 12:00 14,0
8,0 0,0 2,5 on 24-mai-06 20:00 13,9
2,5 0,0 9,8 on 24-mai-06 22:30 13,5
5,0 0,0 3,7 to 25-mai-06 03:30 13,3
2,0 0,0 9,8 to 25-mai-06 05:30 12,9
2,0 0,0 2,5 to 25-mai-06 07:30 12,8
4,0 0,0 7,4 to 25-mai-06 11:30 12,5

..... better still...

=IF(AND(ISNUMBER(E7),E7=0),J5+(D7-J7)/24,J5+(E7-F7)/24)

If e can never be blank then

=IF(E7=0,J5+(D7-J7)/24,J5+(E7-F7)/24)


and there was a typo in my original ....

=if(e7=0;j5+(d7-f7)/24;If(e7="";"";j5+(e7-f7)/24))

"need help in Norway" wrote:

In column J I have the following formula =IF(E7="";"";J5+(E7-F7)/24) and
everything works fine, but my problem is that if a 0 is in column E then the
status days in column J start to go backwards what I need is to add the
deference between column D and F to Column J if there is a 0 in column E but
the old formula needs to work if there is a number like 2,5 hours added to
column E.

Does anyone have any ideas on this???


D E F G H
I J

Est. Actual AFE Start 05-mar-06 11:30 Status
Hrs. Hrs Hrs Day Finished Days
Date Time

18,0 25,5 26,1 to 18-mai-06 07:30 16,0
3,0 1,0 2,5 to 18-mai-06 08:30 15,9
3,0 3,0 0,0 to 18-mai-06 11:30 16,0
18,0 17,5 0,0 fr 19-mai-06 05:00 16,8
18,0 0,0 0,0 fr 19-mai-06 23:00 16,8
4,0 0,0 2,5 lø 20-mai-06 03:00 16,7
105,0 0,0 63,8 on 24-mai-06 12:00 14,0
8,0 0,0 2,5 on 24-mai-06 20:00 13,9
2,5 0,0 9,8 on 24-mai-06 22:30 13,5
5,0 0,0 3,7 to 25-mai-06 03:30 13,3
2,0 0,0 9,8 to 25-mai-06 05:30 12,9
2,0 0,0 2,5 to 25-mai-06 07:30 12,8
4,0 0,0 7,4 to 25-mai-06 11:30 12,5