in excell how do I say "now + 115 hours, then 5 am on the fist saturday after ?

Posted on 2011-04-20
Last Modified: 2012-05-11
I want to find the first saturday @ 5 am  more than 115 hours from now.

I can perform a task at 5 am on any saturday  however I need to let the owner know 115 hours ahead of time.

this is a 1 time deal so I want to use the first satuday at 5 am that fits the requirement.  
Question by:TIMFOX123
    LVL 31

    Accepted Solution

    You can use the CEILING function for this:


    Rob H
    LVL 31

    Expert Comment

    by:Rob Henson
    To explain:

    =NOW() gives time at point of calculation

    Adding 115/24 will add sufficient days and hours.

    =CEILING(###,n) will round up the ### to the next factor of n. As dates are held as a serial number incremented from 01/01/1900 which happened to be a Sunday. The whole number being the date and the decimal portion of a number being the time eg 3am being 0.25 and midday being 0.5 etc

    Therefore counting up in units of 7 will give the whole serial number dates for Saturdays. Then adding 5/24 to the result will be the decimal part of the number for 5am.

    Rob H


    Author Closing Comment

    I am totally impressed

    I would have taken tooooo long to figure this one out


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now