Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts

    help with time in excel

    Hi there in excel when you custom time ie hh.mm when I input it it doesn't do what I type any ideas please

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mar27,

    Ok your custom format has a period (.) between the hours & minutes. However, when you enter the value you must use the colon (:) between the minutes and seconds for Excel to recognize that you are entering a time. If you do this the time will display as 10.45 even though you entered it as 10:45. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by RetiredGeek View Post
    Mar27,

    Ok your custom format has a period (.) between the hours & minutes. However, when you enter the value you must use the colon (:) between the minutes and seconds for Excel to recognize that you are entering a time. If you do this the time will display as 10.45 even though you entered it as 10:45. HTH
    I have done that which has worked on most of my sheet but when I input ie 57:00 I am getting this instead 02/01/1900 09:00:00

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mar27,

    Ok, I'm guessing you're using the dd/mm/yyyy date format (European I'm guessing since you didn't post your location).
    57 Hours = 2 Days 9 Hours thus -> Counting from 1/1/1900 + 2 Days + 9 Hours = 2 Jan 1900 @ 09:00. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Quote Originally Posted by mar27 View Post
    I have done that which has worked on most of my sheet but when I input ie 57:00 I am getting this instead 02/01/1900 09:00:00
    This is correct as you now have 57 hours from 1 Jan 1900 at midnight. hh:mm (however formatted) is clock time not a count of hours and minutes.

    The question is - what are you using the data for? If just displaying an elapsed time then formatting the cell as text will do the trick. It becomes a bit more complicated if you want to use the numbers in a calculation.

  6. #6
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts
    All I am doing is using as a colmum as time it takes to sleep just for input only no date or formula in there but on one row is does what I say
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mar27,

    I'm a bit at a loss to understand your worksheet.

    Row 26 shows 20 hours to fall asleep but only 7:19 in bed?

    Could you please explain the logic between the columns as it will surely help with arriving at a solution.

    BTW: You can use this formula to calculate time in bed. =($A3+1+$C3)-($A3+$B3)
    Place in cell G3 then fill down.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    mar27 (2015-06-27)

  9. #8
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Hi there no formula all that I am doing is using excel to recorded date but it when it imput the time to bed it comes up with what I say just need advice how to stop it thanks

  10. #9
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    You are obviously entering a time of some sort different than what the cell is formatted for. Could you give an example of what you input and the result that shows for that input?

    B.

  11. #10
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts
    all that I am input in line d27 there was a few lines on other sheet the same on this one input time as I set it as 57:00 but then it goes too 02/01/1900 09:00:00 retiredgeek said before but I don't no how to change it thanks

  12. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Try changing the time format to: [HH].MM

    cheers, Paul

  13. #12
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts
    paul it set as hh.mm but still get the 02/01/1900 09:00:00 when I put any thing like 57:00

  14. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You must use the square brackets.

    cheers, Paul

  15. The Following User Says Thank You to Paul T For This Useful Post:

    mar27 (2015-06-27)

  16. #14
    Star Lounger
    Join Date
    Jun 2015
    Posts
    59
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Paul T View Post
    You must use the square brackets.

    cheers, Paul
    tried that paul just getting[57].00 which I wrong, but why all the rest of the time that I have input is correct look at the file above that I have put online please

  17. #15
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    try [h]:mm as the format using a colon instead of a period

Page 1 of 2 12 LastLast

Posting Permissions

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