+ Reply to Thread
Results 1 to 9 of 9

Time Formatting (Number Format?)

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    new york
    MS-Off Ver
    office 365 proplus 1811
    Posts
    13

    Exclamation Time Formatting (Number Format?)

    Hello,

    I've stumbled across a task I need a assistance with I was hoping someone could kindly assist me.

    I have a column of data with hourly time in each cell stored in fractional format.

    For example: 1 Hour and 15 minutes is stored as 1.25. Half an hour (30 Minutes) is stored as 0.5.

    I have thousands of rows of data in this column that I need to format from 1.25 to 01:30.
    I don't want to tamper with the data in the cells by dividing by 24 and then changing the number format. This is for marketing purposes and the data cannot be changed, nor hidden rows and all of that mess.
    I am looking for a way to apply some kind of number format or conditional format that automatically changes this entire column, so if I were to enter 0.5 in a cell, it would automatically become 00:30, ect.

    I've attached a dummy spreadsheet for your reference.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Time Formatting (Number Format?)

    2 and 2:00, for example, have different numeric values. 2:00 is actually 0.08333 (2/24)

    Unfortunately, there is no way to keep one value (2) in a cell but format it to show in the same cell as another value (2:00).

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Time Formatting (Number Format?)

    Hi

    I suppose you can not do what you want. You can not manipulate the format to somehow get the desired result.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,775

    Re: Time Formatting (Number Format?)

    You may try custom format

    0\:00/60

    Place cursor before / and press Ctrl+J a few time to put /60 to next line, then warp text to show only 1 line

    custom.jpg

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Time Formatting (Number Format?)

    Looks like I was proved wrong!

    Nice Bo_Ry.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,775

    Re: Time Formatting (Number Format?)

    63falcondude, Thank you.
    I answered a similar question before somewhere else.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Time Formatting (Number Format?)

    That's very clever.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,775

    Re: Time Formatting (Number Format?)

    63falcondude, shg, Fluff13
    Thank you all very much for the reps and kind comments.
    This is the first time that I get many reps from 1 answer.

  9. #9
    Registered User
    Join Date
    02-12-2019
    Location
    new york
    MS-Off Ver
    office 365 proplus 1811
    Posts
    13

    Re: Time Formatting (Number Format?)

    Bo_Ry you are a genius. Thank you very much for solving.

+ 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] Converting an elapsed time in decimal number format to an actual time :S
    By Spicey_888 in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 08:53 PM
  2. [SOLVED] Convert number format to time format
    By bgates in forum Excel General
    Replies: 3
    Last Post: 11-29-2013, 09:04 AM
  3. Formatting TIME IN mm,ss format
    By naga in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-14-2013, 09:47 PM
  4. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  5. Time Formatting - Not able to sum up the time when converted to text format.
    By sagarshinde01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2013, 11:38 AM
  6. Excel: How to convert time format into number format that can be added?
    By Frances Jones in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2012, 04:10 PM
  7. Converting number format to time format
    By mbarr in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-28-2011, 09:19 AM

Tags for this Thread

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