+ Reply to Thread
Results 1 to 7 of 7

Dividing a Number by a time period in Days and Hours (dd-hh)

  1. #1
    Registered User
    Join Date
    07-27-2020
    Location
    Cardiff, Wales
    MS-Off Ver
    2013
    Posts
    4

    Dividing a Number by a time period in Days and Hours (dd-hh)

    I am analysing data from an Critical Care unit on drug usage. I have cells that state the duration of time that a patient was on the unit in Days and Hours (dd-hh) "x days y hours". Over the period of time on the unit I also have the amount of drug used in mLs, entered as a number in a Cell.

    I want to be able to determine the amount of drug used per day during their stay, as mLs/hr.

    Currently, I am unable to find the appropriate function and formula to calculate this neatly. I attach an example Spreadsheet if this helps.

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Dividing a Number by a time period in Days and Hours (dd-hh)

    Try this:

    =B2/(IFERROR(LEFT(A2,SEARCH("day",A2)-2)*24,0)+IFERROR(MID(A2,SEARCH("hr",A2)-3,2),0))

  3. #3
    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: Dividing a Number by a time period in Days and Hours (dd-hh)

    A slight variation, in C2:

    =B2/(LEFT(A2,FIND(" days",A2)-1)*24 + MID(A2,FIND("days ",A2)+5,2))

    Hope this helps.

    Pete

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dividing a Number by a time period in Days and Hours (dd-hh)

    Possibly in C2 copied down = average per day

    =B2/(((LEFT(A2,2)*24)+MID(A2,9,2)))*24

  5. #5
    Registered User
    Join Date
    07-27-2020
    Location
    Cardiff, Wales
    MS-Off Ver
    2013
    Posts
    4

    Re: Dividing a Number by a time period in Days and Hours (dd-hh)

    Quote Originally Posted by Phuocam View Post
    Try this:

    =B2/(IFERROR(LEFT(A2,SEARCH("day",A2)-2)*24,0)+IFERROR(MID(A2,SEARCH("hr",A2)-3,2),0))
    Thank you ever so much for posting this reply so promptly. It has worked perfectly; albeit I multiplied B2*24/(IFERROR(...... The information gleaned is very informative. Thanks again.

  6. #6
    Registered User
    Join Date
    07-27-2020
    Location
    Cardiff, Wales
    MS-Off Ver
    2013
    Posts
    4

    Re: Dividing a Number by a time period in Days and Hours (dd-hh)

    Thanks Pete - this worked for the vast majority of patient data - it returned an error though when the length of stay was less than 2 days i.e. 1 day 20hrs. Thank you though for replying so promptly, it is very much appreciated.

  7. #7
    Registered User
    Join Date
    07-27-2020
    Location
    Cardiff, Wales
    MS-Off Ver
    2013
    Posts
    4

    Re: Dividing a Number by a time period in Days and Hours (dd-hh)

    Quote Originally Posted by torachan View Post
    Possibly in C2 copied down = average per day
    Thank you torachan - this again worked for the vast majority of patient data - it returned an error though when the length of stay was less than 2 days i.e. 1 day 20hrs. Thank you though for replying so promptly, it is very much appreciated.

+ 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. Replies: 0
    Last Post: 06-11-2015, 03:12 AM
  2. [SOLVED] dividing number with hours and minutes
    By pannam in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2014, 04:19 AM
  3. If time period falls outside of normal working hours how many hours does it add.
    By chrisdromey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2013, 11:56 PM
  4. Calculating number of certain hours within a specified time period
    By cdf27 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2013, 07:19 PM
  5. Replies: 1
    Last Post: 11-02-2011, 06:50 PM
  6. Replies: 1
    Last Post: 11-02-2011, 06:44 PM
  7. Replies: 8
    Last Post: 02-23-2005, 06:58 PM

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