+ Reply to Thread
Results 1 to 7 of 7

How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do maths

  1. #1
    Registered User
    Join Date
    06-16-2021
    Location
    Bristol, UK
    MS-Off Ver
    2002 at home/ 365 at work
    Posts
    2

    Question How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do maths

    I am going out of my mind. I really don't want to enter "10:00" or "09:45" everytime - I use a numpad and I don't wanna keep "Shift+;"ing.

    I have three columns: Shift start / Time started / Lateness

    I want to enter "0930"[b1] / "1030"[b2] / and [=b1-b2] outputs "45"

    e.g. "0930"/"1100"/"90" or "0930"/"1100"/"1:30"

    - Or at least something like this that would be simple to use. This seems like such a straight forward function but I can't figure out how to make it work!

    - see attachment for example

    Excel Type (Windows)

    Excel Version (2002 at home Office 365 at work)

    Excel Environment (desktop)

    Knowledge Level (Intermediate I guess)
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do math

    Chip Pearson's site has a section on Easy Date & Time Entry, available here:

    http://www.cpearson.com/Excel/DateTimeEntry.htm

    This will do what you want to achieve.

    Hope this helps.

    Pete

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do math

    If you are just looking to use B1-B2, it won't work without using VBA. If you just want a formula to achieve the result, then (assuming B11 and C11 are formatted as TEXT as entered with 4 digits as per example)in D11 (formatted as 24hr time)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do math

    Hi and welcome to the forum

    Why do you indicate 0900 to 1015 is 45 minutes rather than 75 minutes

    In D11 use

    =60*(INT(C11/100)+MOD(C11,100)/60-INT(B11/100)+MOD(B11,100)/60)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do math

    One way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Registered User
    Join Date
    06-16-2021
    Location
    Bristol, UK
    MS-Off Ver
    2002 at home/ 365 at work
    Posts
    2

    Re: How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do math

    Quote Originally Posted by Richard Buttrey View Post
    Why do you indicate 0900 to 1015 is 45 minutes rather than 75 minutes
    Cos apparently I can't do maths XD

    Your solution woked perfect btw - you are a genius!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: How to enter a time without a date so "1000"="10:00"≠"26/09/1902 00:00:00" and do math

    Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Brief formula to convert time values to "shift1", "shift2", "shift3".
    By darekpawel in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-11-2019, 07:05 AM
  2. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  3. Replies: 2
    Last Post: 07-13-2018, 11:52 PM
  4. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  5. Replies: 1
    Last Post: 01-22-2016, 09:21 AM
  6. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  7. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1