Convert int to date

Posted on 2010-01-04
Last Modified: 2012-05-08
Hello Experts,

I had an error today with a formula to calculate the time spent in a Task List.

I used: =TEXT([Due Time]-[Start Time]],"h:mm")

But I did not have the expected result (it not calculates if more than 24 hours).

So I applied this formula: =INT(([Due Date]-[Start Date])*1440)
It shows the minutes spent.


How do I convert this integer value to hours: minutes?,


Start Time: 10:00 AM
Due Time: 11:15 AM
Result: 75

Desired Result: 1:15


Question by:br_ms
    LVL 24

    Accepted Solution

    you need the modulo operator to do that.

    hours = int(INT(([Due Date]-[Start Date])*1440) / 60)
    minutes = INT(([Due Date]-[Start Date])*1440) % 60

    Then you do something like text( hours & ":" & minutes)

    Author Closing Comment

    Ok, thx

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    A question that is asked often, is how to generate sequential numbers in InfoPath Forms. The best way to achieve this is to use a SQL database, along with a stored procedure and a web service to connect Forms Services to the DB. The first thing t…
    The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    728 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

    22 Experts available now in Live!

    Get 1:1 Help Now