?
Solved

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

Posted on 2011-04-21
10
Medium Priority
?
255 Views
Last Modified: 2012-08-13
I am having problems trying to figure out this macro.
=CEILING(NOW()+419/24,7)+5/24

I want the first time it is 5 am, saturday that is at least  396 hours from now
I also want the first time it is 5 am,Sunday  that is at least  415  hours from now
0
Comment
Question by:TIMFOX123
  • 5
  • 3
  • 2
10 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35440756
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
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35440805
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

Open in new window

for the first sunday @ 5 am, use :  
=CEILING(now(),7) + 29/24

Open in new window

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35440827
if you want the number of hours till the next saturday, 5am :

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

Open in new window


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 35440859
but to answer your question :

I want the first time it is 5 am, saturday that is at least  396 hours from now
=CEILING(NOW()+396/24,7)+5/24

Open in new window


I also want the first time it is 5 am,Sunday  that is at least  415  hours from now
=CEILING(NOW()+415/24,7)+29/24

Open in new window

0
 

Author Comment

by:TIMFOX123
ID: 35440898
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

 
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35441199
the ceiling rounds it up

so 6.9 = 7 and 6.0001 = 7 and 6.0 = 6
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35441443
0
 

Author Comment

by:TIMFOX123
ID: 35442776
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 :)

0
 

Author Closing Comment

by:TIMFOX123
ID: 35442789
thank you both
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35442894
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question