Hi all

I have been trying to solve this formula for some time but with no success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00

=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after 12:00, but if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise just proceed
with it as shown.

Any suggestions please, and thank you

Issam,

I can't really follow your logic: why do you want to add 7 hours?

Perhaps an explanation of your expected results from various inputs.

Would 11:00 be 11:00 PM, perhaps?

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Hi all

I have been trying to solve this formula for some time but with no
success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00


=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after 12:00, but
if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise just
proceed
with it as shown.

Any suggestions please, and thank you

Bernie, thanks for your assistance.

The reason for adding 7 hours is because that is the length of the time for
the activity. Therefore the logic behind it is:
Start activity 1 today at 10.00 for example and it ends at 17.00 (cell e7).
The start of the nect activity should not be
before 07.00 (Cell F7) next day. All times are based on 24 hour day

thanks again

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

I can't really follow your logic: why do you want to add 7 hours?

Perhaps an explanation of your expected results from various inputs.

Would 11:00 be 11:00 PM, perhaps?

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Hi all

I have been trying to solve this formula for some time but with no
success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00



=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after 12:00,
but
if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise just
proceed
with it as shown.

Any suggestions please, and thank you

Issam,

That doesn't really explain your cells. If start time is in cell E7, then
the end time is simple seven hours later, or

=E7 + 7/24

formatted for time.

Is there a logical connection between E7 and F7? I don't see one....
perhaps more examples, with the values of E7 and F7, and what you want the
formula to return in each case.

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Bernie, thanks for your assistance.

The reason for adding 7 hours is because that is the length of the time
for
the activity. Therefore the logic behind it is:
Start activity 1 today at 10.00 for example and it ends at 17.00 (cell
e7).
The start of the nect activity should not be
before 07.00 (Cell F7) next day. All times are based on 24 hour day

thanks again

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

I can't really follow your logic: why do you want to add 7 hours?

Perhaps an explanation of your expected results from various inputs.

Would 11:00 be 11:00 PM, perhaps?

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Hi all

I have been trying to solve this formula for some time but with no
success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00




=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after 12:00,
but
if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise just
proceed
with it as shown.

Any suggestions please, and thank you

Bernie,

Sorry for causig some confusion. Hope this explains it better.

E7 is the time the first activity (work shift) ends.
F7 is the time the second activity (work sift) starts
The time laps between E7 and f7 should be more that 12 hours.
If it is less than then 12 hours, I would like the "ERR" to show
G7 is the end of the second activity.

Hope this explain it and thanks for all your help
Issam

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

That doesn't really explain your cells. If start time is in cell E7, then
the end time is simple seven hours later, or

=E7 + 7/24

formatted for time.

Is there a logical connection between E7 and F7? I don't see one....
perhaps more examples, with the values of E7 and F7, and what you want the
formula to return in each case.

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Bernie, thanks for your assistance.

The reason for adding 7 hours is because that is the length of the time
for
the activity. Therefore the logic behind it is:
Start activity 1 today at 10.00 for example and it ends at 17.00 (cell
e7).
The start of the nect activity should not be
before 07.00 (Cell F7) next day. All times are based on 24 hour day

thanks again

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

I can't really follow your logic: why do you want to add 7 hours?

Perhaps an explanation of your expected results from various inputs.

Would 11:00 be 11:00 PM, perhaps?

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Hi all

I have been trying to solve this formula for some time but with no
success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00





=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after
12:00,
but
if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise just
proceed
with it as shown.

Any suggestions please, and thank you

Issam,

=IF((1+F7-E7)<0.5,"Error! Too early!",F7+7/24)

Assumes that the time value in E7 is correct (entered as or calced as 11:00
PM not just 11:00).

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Bernie,

Sorry for causig some confusion. Hope this explains it better.

E7 is the time the first activity (work shift) ends.
F7 is the time the second activity (work sift) starts
The time laps between E7 and f7 should be more that 12 hours.
If it is less than then 12 hours, I would like the "ERR" to show
G7 is the end of the second activity.

Hope this explain it and thanks for all your help
Issam

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

That doesn't really explain your cells. If start time is in cell E7,
then
the end time is simple seven hours later, or

=E7 + 7/24

formatted for time.

Is there a logical connection between E7 and F7? I don't see one....
perhaps more examples, with the values of E7 and F7, and what you want
the
formula to return in each case.

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Bernie, thanks for your assistance.

The reason for adding 7 hours is because that is the length of the
time
for
the activity. Therefore the logic behind it is:
Start activity 1 today at 10.00 for example and it ends at 17.00 (cell
e7).
The start of the nect activity should not be
before 07.00 (Cell F7) next day. All times are based on 24 hour day

thanks again

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

I can't really follow your logic: why do you want to add 7 hours?

Perhaps an explanation of your expected results from various inputs.

Would 11:00 be 11:00 PM, perhaps?

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Hi all

I have been trying to solve this formula for some time but with
no
success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00






=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after
12:00,
but
if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise
just
proceed
with it as shown.

Any suggestions please, and thank you

Bernie,

Thanks it works great.!!!

Issam
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

=IF((1+F7-E7)<0.5,"Error! Too early!",F7+7/24)

Assumes that the time value in E7 is correct (entered as or calced as
11:00
PM not just 11:00).

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Bernie,

Sorry for causig some confusion. Hope this explains it better.

E7 is the time the first activity (work shift) ends.
F7 is the time the second activity (work sift) starts
The time laps between E7 and f7 should be more that 12 hours.
If it is less than then 12 hours, I would like the "ERR" to show
G7 is the end of the second activity.

Hope this explain it and thanks for all your help
Issam

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

That doesn't really explain your cells. If start time is in cell E7,
then
the end time is simple seven hours later, or

=E7 + 7/24

formatted for time.

Is there a logical connection between E7 and F7? I don't see one....
perhaps more examples, with the values of E7 and F7, and what you want
the
formula to return in each case.

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Bernie, thanks for your assistance.

The reason for adding 7 hours is because that is the length of the
time
for
the activity. Therefore the logic behind it is:
Start activity 1 today at 10.00 for example and it ends at 17.00
(cell
e7).
The start of the nect activity should not be
before 07.00 (Cell F7) next day. All times are based on 24 hour day

thanks again

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Issam,

I can't really follow your logic: why do you want to add 7 hours?

Perhaps an explanation of your expected results from various
inputs.

Would 11:00 be 11:00 PM, perhaps?

HTH,
Bernie
MS Excel MVP


"Issam LAdki" wrote in message
...
Hi all

I have been trying to solve this formula for some time but with
no
success.

Cell E7 contains Time for end of previous activity, e.g. 14:00
Cell F7 contains Time for start of next activity, e.g. 11:00







=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0), (IF(F7<MOD(E7+TIME(12,0,0)
,1),"ERR",F7+TIME(7,0,0))))

This formula seems to work great if the end time (E7) is after
12:00,
but
if
it is before 12:00, then I always get "ERR".

What I have been trying to do is to add
IF (F7 <12:00, F7=12:00 and proceed with the fromula, otherwise
just
proceed
with it as shown.

Any suggestions please, and thank you