+ Reply to Thread
Results 1 to 10 of 10

Add/Subtract Time over a new day?

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Add/Subtract Time over a new day?

    I have a spreadsheet where I am tracking time spent on sleep (11 week old baby, yay!), but some of the sleep sessions roll into a new day. For example, one goes from 9:00pm on January 10th to 3:25am on January 11th. Through the day it's no problem to track the hours spent sleeping but I get an error when the formula crosses a date line. Can anyone help me?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Add/Subtract Time over a new day?

    How are you storing the date and time? All in one cells or separated?

    Data Range
    A
    B
    1
    10-Jan-18 09:00 PM
    2
    11-Jan-18 03:25 AM
    3
    6:25:00
    $A$3 =((A2+(A2<A1))-A1)
    4
    5
    1/10/2018
    9:00:00 PM
    6
    1/11/2018
    3:25:00 AM
    7
    6:25:00
    $A$7 =((A6+B6)+(A6<A5)-(A5+B5))

    Custom format the hours slept with >> [h]:mm:ss
    Last edited by jeffreybrown; 01-20-2018 at 08:43 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Add/Subtract Time over a new day?

    Hi Jeff - They are in separate cells. I'd like to keep them in separate cells if possible - I'm looking at patterns across dates, days of the week, and times of day, so it's helpful to have all of those in separate fields. Thank you so much for your help!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Add/Subtract Time over a new day?

    I edited post #2

    And btw, congrats on the baby, but don't fret about the sleep. Even when they grow up you can't simply get enough.

    Never mind the fact that for some of us, we're still losing sleep with grandchildren
    Last edited by jeffreybrown; 01-20-2018 at 08:47 PM.

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Add/Subtract Time over a new day?

    Never mind the fact that for some of us, we're still losing sleep with grandchildren
    You said it brother!!
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Add/Subtract Time over a new day?

    Are you able to use PowerQuery?
    Attached Files Attached Files

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Add/Subtract Time over a new day?

    And Tom, as you probably also know very well, they have enough energy you don't need to compound it with sugar.

    Our lovely daughter dropped our grandchildren off today with Kit Kat's in their hands.

    Not sure I thanked her, but probably won't be any time soon...

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Add/Subtract Time over a new day?

    Just do the same when they get to go back home, only DOUBLE the dose! That's how I thank mine when they do it to us.

  9. #9
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Add/Subtract Time over a new day?

    Ha!! Thank you! I'm not really fretting over it too much - more than anything I'm just enjoying my little tracking project She's been having some nap issues for the last few weeks - wakes up after 40 minutes with alarming consistency, though not for every nap - the consistency is the 40-minute mark, not which nap she wakes up for. So I'm doing some investigating, and I have found that almost every 3:15-ish nap is a bad nap. So, why? The sun sets on that side of the house, so maybe it's not dark enough in her room - but no, I darkened the room with a sheet for three straight days, and nothing changed. So, on to the next possibility! I'm pretty sure the answer is that she's an infant. :D She has slept through the night a couple of times though, so I'm also looking for correlation between those days and nights - were they both Fridays? Were they both followed by days with 6+ hours of awake time? lol. By the time you figure one thing out, everything changes anyway. But we are having the time of our lives!

  10. #10
    Forum Contributor
    Join Date
    10-02-2008
    Location
    Berkeley, CA
    MS-Off Ver
    Windows 2007
    Posts
    105

    Re: Add/Subtract Time over a new day?

    _____A______________B_____
    1. Before ________ 1/18/18 22:00 <--- Format Date
    2. After _________ 1/20/18 09:00 "
    3. Difference __________ 1 11:00 <-- Special Format "d hh:mm" Formula "=B2-B1"

    Mac
    Last edited by CaptMac; 01-20-2018 at 11:33 PM. Reason: Formatting

+ 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] How to subtract Time Values contained in Labels on a userform - Time Elapsed Live Clock
    By deadeye_draken in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-18-2017, 06:44 AM
  2. Subtract a variable time with a fixed time
    By striker7770 in forum Excel General
    Replies: 1
    Last Post: 09-04-2016, 04:02 PM
  3. [SOLVED] How to subtract time without entering colon and retain time format?
    By blmholland in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 10:36 PM
  4. Subtract 15minutes from start time in a time range
    By JMS727 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-06-2013, 10:35 PM
  5. [SOLVED] Subtract date/time from data/time and get difference in minutes
    By zit1343 in forum Excel General
    Replies: 2
    Last Post: 06-18-2012, 11:23 AM
  6. how to subtract date and time to get inter-arrival time
    By whattodo in forum Excel General
    Replies: 1
    Last Post: 05-28-2012, 12:38 AM
  7. Replies: 3
    Last Post: 02-22-2011, 01:53 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