+ Reply to Thread
Results 1 to 6 of 6

Time formats and time data problem.

  1. #1
    Registered User
    Join Date
    11-20-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    4

    Time formats and time data problem.

    Hi,

    I'm trying to make an editable sample rota for a business plan template and having some troubles getting time data to work how I want it to. I've tried googling it, but keep getting information on setting the format, which I have done.

    The business is a bar, so I am trying to input times like 12:00 in E5, 01:30 in E6, and F5&6 are merged together with the following formula:

    =IF(E6<E5,((E6-E5)+12),(E6-E5))

    All these cells are formatted: custom - hh:mm

    So end time minus start time to get hours, with a 12 hour adjustment where needed.
    This works fine, and is repeated for each day of the week.
    In column S, S5&6 merged together, I have a week total with formula:

    =F5+H5+J5+L5+N5+P5+R5

    This cell is formatted: custom - [h]:mm;@ - I use this as the above formatting would return 00:00 instead of 48:00.

    Now I've established the problem, shown below, in this example I only have one days work on the rota:
    Start 12:00 Cell E5 shows 12:00
    End 01:30 Cell E6 shows 01:30
    F5&6 shows 13:30
    BUT in column S it shows 277:30

    If instead of End 01:30 if I type "25:30", then it displays as 01:30, F5&6 display 13:30, but column S is correct, and also displays 277:30.

    Even this seems odd, if the number were 181:30 then its added a week. (24*7+13.5) so I don't know where this comes from.

    I need to share the document around and want to make it as intuitively usable as possible, so I'm keen to get rid of this, but the only way I can think of would be a giant table with lots of lookups.
    Very clunky!

    What is a more elegant solution? Is there better formatting I could use, or modify one of the formulae?

    Thank you,

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

    Re: Time formats and time data problem.

    Your first formula is incorrect, as you are effectively adding 12 days rather than 12 hours (times are stored internally by Excel as fractions of a 24-hour day). It needs to be:

    =IF(E6<E5,((E6-E5)+0.5),(E6-E5))

    although you don't really need so many brackets. There are other ways that this formula could be written, as well.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-20-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    4

    Re: Time formats and time data problem.

    Thank you for the quick reply,

    Apologies for the many brackets - I am pretty much entirely self taught on excel so I am sure I have numerous bad habits.

    Unfortunately that doesn't work:

    Changing F5&6 to:

    =IF(E6<E5,((E6-E5)+0.5),(E6-E5))

    start 12:00
    end 01:30

    column F returns: 01:30 not 13:30

    start 12:00
    end 25:30 (displays 01:30)

    column F returns correctly: 13:30

    If I change:
    Start 14:00
    end 25:30 (displays 01:30)

    column F returns correctly: 13:30

    But if I change:
    Start 14:00
    End 01:30

    Column F returns: ###############################################

    In all scenarios Column S returns exactly as Column F.

    So, it has somewhat fixed the problem- column S gives no discrepancies to column F, but column F no longer shows correctly.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-28-2014
    Location
    Florida
    MS-Off Ver
    Excel 2007, 2010, 2011 Mac. 2013,2016. I use 2011 and 2016 the most
    Posts
    91

    Re: Time formats and time data problem.

    =if(e5>e6,e6-e5+1,e6-e5)

  5. #5
    Registered User
    Join Date
    11-20-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    4

    Re: Time formats and time data problem.

    Quote Originally Posted by Jamesera27 View Post
    =if(e5>e6,e6-e5+1,e6-e5)
    Thanks! That seems to work. Typing both 01:30 or 25:30 both display as 01:30 and the correct hours show in both Column F and Column S!
    Great...

    Any chance you can explain why it works? What is the "+1" adding, how should I have been able to know that? Does it add a number of hours, or days? I'm keen to learn as well as fix problems here...

    Thank you

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,163

    Re: Time formats and time data problem.

    I'm keen to learn as well as fix problems here...
    A couple of ideas to support your research into how Excel works with date/time serial numbers. These are the main things I have used to become more familiar with date/time data in excel:

    1) A page like this that explain how excel stores and uses date/time as serial numbers http://www.cpearson.com/Excel/datetime.htm
    2) As a learning step, have a column/row adjacent to your date/time calculations that mirror the calculation without formatting (see example). Enter different times in A2 and A3, see what their underlying serial number/value is in B2 and B3, then see the result of the calculation in A4 and B4. You can also select A4 and execute the "Evaluate formula" command to step through the IF() function.

    Those two ideas should help you understand what is going on.
    Attached Files Attached Files
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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] Time variances and time formats
    By noreenh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2014, 02:48 AM
  2. [SOLVED] Countif Time is PM in mixed data formats
    By Grimace in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2012, 10:57 PM
  3. [SOLVED] Charting data vs. 2 different time formats
    By Lminer76 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 08-01-2012, 11:31 PM
  4. Converting different time formats to useable data.
    By Yappa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-25-2008, 11:10 AM
  5. sum time formats
    By doowop5000 in forum Excel General
    Replies: 1
    Last Post: 02-08-2008, 11:22 AM
  6. Date Time Problem - stop updating that each time the document is opened
    By Dreammy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-28-2007, 03:31 PM
  7. Help with time formats
    By [email protected] in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2006, 10:45 AM
  8. [SOLVED] Time Formats
    By Fran in forum Excel General
    Replies: 1
    Last Post: 05-09-2005, 09:06 PM

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