Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
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,B1TIME(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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201001/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
How about this
=WORKDAY(IF(OR(WEEKDAY(A1,2)5,B1TIME(16,0,0)),WO RKDAY(A1,1),A1),3) HTH Bob "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a271b916b79b0@uwe... 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,B1TIME(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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201001/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
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? Bob Phillips wrote: How about this =WORKDAY(IF(OR(WEEKDAY(A1,2)5,B1TIME(16,0,0)),W ORKDAY(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 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
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 "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a271b916b79b0@uwe... 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,B1TIME(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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201001/1 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
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 David Biddulph wrote: 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 -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
Are you sure, 18/01 07:)) returns 21/01/2010 for me (as you see, I do add 3
not 4) HTH Bob "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a2723e79fd5d7@uwe... 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? Bob Phillips wrote: How about this =WORKDAY(IF(OR(WEEKDAY(A1,2)5,B1TIME(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 -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
I think I meant "full house" rather than "full horse". Too long a day. :-(
-- David Biddulph "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a272a18a4a0b6@uwe... 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 David Biddulph wrote: 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 -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
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 Bob Phillips wrote: 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 -- Message posted via http://www.officekb.com |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
Change B1 to MOD(A1,1)
-- David Biddulph "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a2757beaed786@uwe... 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 Bob Phillips wrote: 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 -- Message posted via http://www.officekb.com |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
add 4 days
thank you very much David
Works perfectly with the MOD adjustment Regards Brian David Biddulph wrote: 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] Thanks in advance Brian Thompson -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201001/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to pull <=14 Days, <=30 Days, 30 Days from a date column | Excel Discussion (Misc queries) | |||
Employee days worked (-Holidays, -weekends, Snow Days, etc) | Excel Discussion (Misc queries) | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions |