add 4 days

[BNT1 via OfficeKB.com]

Hi
I got this formula from one of my old treads, that Bob Phillips sorted for me
in 2006 (search - add 4 days).

=WORKDAY(A1,IF(OR(WEEKDAY(A1,2)>5,B1>TIME(16,0,0)),5,4))

I cannot seem to get the formula working when the hours are adjusted. If
using the first example, returns 21/01/10, but when adjust the time to say 21:
00:00 hrs the date does not change

Examples

Mon 18/01/10 07:00:00 result should read 21/01/10
Mon 18/01/10 16:51:00 result should read 22/01/10
Sat 23/01/10 any time result should read 28/01/10
Sun 24/01/10 any time result should read 28/01/10
Mon 6/3 00:51:00 result should be 10/3
In short, anything after 1600hrs the calculation would start from the
following day, weekends are not counted at all, and the calulation will be
from the Monday. (counting before 1600hrs as day one)

Can anyone advise where i'm going wrong?
Thanks in advance
Brian Thompson

 

[Bob Phillips]

How about this

=WORKDAY(IF(OR(WEEKDAY(A1,2)>5,B1>TIME(16,0,0)),WORKDAY(A1,1),A1),3)

HTH

Bob

 

[BNT1 via OfficeKB.com]

thanks for the speedy responce

using 18/01/10 07:44 , returns 22/1/10,
counting 18/1/10 as 1 day, 19th as 2nd day, 20th as 3rd day and 21st and day
4 (so i surpose i should be adding 3 days not 4)
Incidentally, when altering the time to, after 1600hrs, the calculation does
not alter to the next day?

any ideas?

How about this

=WORKDAY(IF(OR(WEEKDAY(A1,2)>5,B1>TIME(16,0,0)),WORKDAY(A1,1),A1),3)

HTH

Bob

Hi
I got this formula from one of my old treads, that Bob Phillips sorted for

[quoted text clipped - 22 lines]

Thanks in advance
Brian Thompson

 

[David Biddulph]

There are rather too many things which I don't understand in what you are
saying.

Your first example returns not 21/1/10 but 22/1/10, which is what you would
expect for 4 days on from 18/1/10.
You say "but when adjust the time to say 21:00:00 hrs the date does not
change", but that is not true. For 21:00, or for other times beyond 16:00,
the date goes on to 25/1/10 (the Monday, as that is the next working day
after Fri 22/1/10).
Hence what you say for your second example "Mon 18/01/10 16:51:00 result
should read 22/01/10" is also untrue.
You've said that for weekends you would start your count from Monday, hence
agasin you are wrong when you say:
"Sat 23/01/10 any time result should read 28/01/10
Sun 24/01/10 any time result should read 28/01/10",
as both of these should (and do) give 29/1/10.
You've completed the full hourse by saying "Mon 6/3 00:51:00 result should
be 10/3", when it should be (and is) 12/3/10.

Overall you seem to be a day adrift, so I wonder whether you intended to use
3 and 4 days in your formula, rather than 4 and 5?
Additionally, I don't understand why you say that the date doesn't change
for times beyond 16:00, because it does.

 

[BNT1 via OfficeKB.com]

David

you are correct with the "full hourse" reference Monday 6/3. This was
copied/pasted from original post and i did not amend. i will look through and
study your comments at home to see where i've gone wrong
thanks for your patence
Brian

There are rather too many things which I don't understand in what you are
saying.

Your first example returns not 21/1/10 but 22/1/10, which is what you would
expect for 4 days on from 18/1/10.
You say "but when adjust the time to say 21:00:00 hrs the date does not
change", but that is not true. For 21:00, or for other times beyond 16:00,
the date goes on to 25/1/10 (the Monday, as that is the next working day
after Fri 22/1/10).
Hence what you say for your second example "Mon 18/01/10 16:51:00 result
should read 22/01/10" is also untrue.
You've said that for weekends you would start your count from Monday, hence
agasin you are wrong when you say:
"Sat 23/01/10 any time result should read 28/01/10
Sun 24/01/10 any time result should read 28/01/10",
as both of these should (and do) give 29/1/10.
You've completed the full hourse by saying "Mon 6/3 00:51:00 result should
be 10/3", when it should be (and is) 12/3/10.

Overall you seem to be a day adrift, so I wonder whether you intended to use
3 and 4 days in your formula, rather than 4 and 5?
Additionally, I don't understand why you say that the date doesn't change
for times beyond 16:00, because it does.
--
David Biddulph

Hi
I got this formula from one of my old treads, that Bob Phillips sorted for

[quoted text clipped - 22 lines]

Thanks in advance
Brian Thompson

 

[Bob Phillips]

Are you sure, 18/01 07) returns 21/01/2010 for me (as you see, I do add 3
not 4)

HTH

Bob

thanks for the speedy responce

using 18/01/10 07:44 , returns 22/1/10,
counting 18/1/10 as 1 day, 19th as 2nd day, 20th as 3rd day and 21st and
day
4 (so i surpose i should be adding 3 days not 4)
Incidentally, when altering the time to, after 1600hrs, the calculation
does
not alter to the next day?

any ideas?

How about this

=WORKDAY(IF(OR(WEEKDAY(A1,2)>5,B1>TIME(16,0,0)),WORKDAY(A1,1),A1),3)

HTH

Bob

Hi
I got this formula from one of my old treads, that Bob Phillips sorted
for

[quoted text clipped - 22 lines]

Thanks in advance
Brian Thompson

 

[David Biddulph]

I think I meant "full house" rather than "full horse". Too long a day. :-(
--
David Biddulph

David

you are correct with the "full hourse" reference Monday 6/3. This was
copied/pasted from original post and i did not amend. i will look through
and
study your comments at home to see where i've gone wrong
thanks for your patence
Brian

There are rather too many things which I don't understand in what you are
saying.

Your first example returns not 21/1/10 but 22/1/10, which is what you
would
expect for 4 days on from 18/1/10.
You say "but when adjust the time to say 21:00:00 hrs the date does not
change", but that is not true. For 21:00, or for other times beyond
16:00,
the date goes on to 25/1/10 (the Monday, as that is the next working day
after Fri 22/1/10).
Hence what you say for your second example "Mon 18/01/10 16:51:00 result
should read 22/01/10" is also untrue.
You've said that for weekends you would start your count from Monday,
hence
agasin you are wrong when you say:
"Sat 23/01/10 any time result should read 28/01/10
Sun 24/01/10 any time result should read 28/01/10",
as both of these should (and do) give 29/1/10.
You've completed the full hourse by saying "Mon 6/3 00:51:00 result should
be 10/3", when it should be (and is) 12/3/10.

Overall you seem to be a day adrift, so I wonder whether you intended to
use
3 and 4 days in your formula, rather than 4 and 5?
Additionally, I don't understand why you say that the date doesn't change
for times beyond 16:00, because it does.
--
David Biddulph

Hi
I got this formula from one of my old treads, that Bob Phillips sorted
for

[quoted text clipped - 22 lines]

Thanks in advance
Brian Thompson

 

[BNT1 via OfficeKB.com]

Bob / David
One thousand apologies to you both, spent a long time trying to see why you
were getting the result and I was not

Now realise that the original post in 2006 had the date in A1 and time in B1.

My new data has the date and time all in A1

Could I ask that you patience holds out a little longer and amend the formula
to reflect
(18/01/10 07:00:00) being in one cell

Yours very gratefully

Brian

Are you sure, 18/01 07) returns 21/01/2010 for me (as you see, I do add 3
not 4)

HTH

Bob

thanks for the speedy responce

[quoted text clipped - 22 lines]

 

[David Biddulph]

Change B1 to MOD(A1,1)
--
David Biddulph

Bob / David
One thousand apologies to you both, spent a long time trying to see why
you
were getting the result and I was not

Now realise that the original post in 2006 had the date in A1 and time in
B1.

My new data has the date and time all in A1

Could I ask that you patience holds out a little longer and amend the
formula
to reflect
(18/01/10 07:00:00) being in one cell

Yours very gratefully

Brian

Are you sure, 18/01 07) returns 21/01/2010 for me (as you see, I do add
3
not 4)

HTH

Bob

thanks for the speedy responce

[quoted text clipped - 22 lines]

Thanks in advance
Brian Thompson

 

[BNT1 via OfficeKB.com]

thank you very much David

Works perfectly with the MOD adjustment

Regards

Brian

Change B1 to MOD(A1,1)
--
David Biddulph

Bob / David
One thousand apologies to you both, spent a long time trying to see why

[quoted text clipped - 28 lines]