Variable time calculation

  • Thread starter Thread starter Issam LAdki
  • Start date Start date
I

Issam LAdki

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
 
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
 
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
 
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
 
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
 
Bernie,

Thanks it works great.!!!

Issam
Bernie Deitrick said:
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



=IF(OR((ISTEXT(E7)),(ISBLANK(E7))),F7+TIME(7,0,0),(IF(F7<MOD(E7+TIME(12,0,0)
Click to expand...
 
Back
Top