explain this formula to me =CEILING(NOW()+419/24,7)+5/24

now() gives us the current date and time (number of days from the null-date)
first you add 419 hours (17 days and 11 hours).
then you use the ceiling to round it off to multiples of 7 (that is: you search for the start of the next week)
then you add 5 hours

this macro will give you the first saturday @ 5am with an offset of 17 days and 11 hours.

for the first saturday @ 5am, use :

=CEILING(now(),7) + 5/24

Select allOpen in new window

for the first sunday @ 5 am, use :  

=CEILING(now(),7) + 29/24

Select allOpen in new window

if you want the number of hours till the next saturday, 5am :

=24*(CEILING(now(),7)+5/24 - now())

Select allOpen in new window

round off or round down ?

then you use the ceiling to round it off to multiples of 7

Round off makes 6.9 = 7 and   6.1 = 6
Round down makes 6.9 = 6   and 6.1 = 6

 

the ceiling rounds it up

so 6.9 = 7 and 6.0001 = 7 and 6.0 = 6

Did your original question not answer it??

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26968055.html

robhenson:

When I tried make this work, for myself I could not get it to work .

your code worked flawlessly  

I just did not work as flawlessly :)

thank you both

You should have posted a comment on the original question rather than starting a new one, it would not have cost you any thing - points etc.

Cheers
Rob H