Calculate the date between to cells

    I am needing to find the number of days from column 'D' 'Creation Date' to column 'Q' 'Class Start Date'. This formula can be placed in the column 'AY'. The issue using DATEIF is the formatting in column 'D2'. It is displayed as ##:##.#, but is 1/23/2023 6:22:47 AM and if I format the cell as Number > Date, I am still unable to calculate the date difference. Is there a formula or a macro that can do this?


    Thanks

    Also be aware that formatting is the way XL shows contents to the outside world. The underlying value is a number. e.g. 0.6666666 is the same as 16:00:00 or 04:00:00 PM, etc.. Formatting will NOT change the underlying value which XL will use for calculations

    Edited once, last by Carim ().

    Like 1

    Hi,


    Pecoflyer is 100% right ...!!!


    Once you know (and understand...) that Dates are ONLY a way to display serial numbers ...everything is simple !!!


    Actually, in Excel, Dates do start on 1st January 1900 with a date serial number of 1


    Now you can easily go ahead with your usual additions or subtractions, e.g :

    =TODAY()-DATE(2023,1,1) will produce the number of days between Today and the 1st of January 2023


    By the way, if you want to fully master the topic Excel date and Time, see this excellent explanation :

    Everything you need to know about working with dates and time in Excel
    One of the most challenging aspects of Excel is working with dates and times. It's certainly one of the most common topics I get asked about. Download my…
    www.myonlinetraininghub.com


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Like 1

    PETRODATAjames


    Hi,


    Have you managed to fix your "Date" problem ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login