Day into Hours into Minutes Calculator Spreadsheet

**jeph_oliver** · 01-20-2015, 01:21 PM

Hello everyone,

I'm sorry if this is already a forum and if it is, please direct me to this. I currently do not have the time to scan around and find something close to what I'm looking for. And I sincerely apologize!

What I am looking for is a formula that will help me calculate the day and specific hour between an event. My job deals with deviations and on some occasions there are days that go by before we have a return. I had made a spreadsheet but it wasn't as good as it could be. Here is a time frame of what I am looking to find/create a formula with.

The original event was scheduled for 1830 on 8/29. The person didn't make it back until 1255 on 9/3. What I am wanting to have is a formula that will calculate the days and hours in between those times.

Is this possible?

Thanks so much for everything.

**mcmahobt** · 01-20-2015, 01:40 PM

See attached workbook for example.

Hope this helps!

**jeph_oliver** · 01-20-2015, 01:48 PM

Wow, thank you so much! I forgot to add I also need the minutes as well! Could you please add that to the formula as well?! Thanks so much!

**mcmahobt** · 01-20-2015, 01:51 PM

Try this in B4. If you don't mind the minute(s) with/without the "s", then that should work. Otherwise, the formula would get a little more messy:

Please Login or Register  to view this content.

**jeph_oliver** · 01-20-2015, 02:29 PM

I am getting one minute and that obviously is not correct. Do you know why I could be getting that?

**mcmahobt** · 01-20-2015, 02:35 PM

Use this method instead if the above is giving you problems.

**jeph_oliver** · 01-20-2015, 02:39 PM

The formula is showing up simply as B2-B1. I don't know why it's doing that for me.

**mcmahobt** · 01-20-2015, 02:45 PM

As in all you see is the formula =B2-B1, not the results? If that is the case, go to File -> Options -> Formulas and select Automatic under Workbook Calculation. Otherwise, I will walk you through the simple steps to retrieve the results.

**jeph_oliver** · 01-20-2015, 02:48 PM

Opening up your spreadsheet, I went to copy your formula and that's all it had in the formula section. Let me try your suggestion... Yeah, that is already selected.

**jeph_oliver** · 01-20-2015, 02:55 PM

Opening up your spreadsheet, I went to copy your formula and that's all it had in the formula section. Let me try your suggestion... Yeah, that is already selected.

**mcmahobt** · 01-20-2015, 02:57 PM

Copying the formula won't work because I altered the formatting of the text. Navigate to format cell and you should see the Custom formatting I applied. If you apply that to your workbook (assuming your dates are formatted as dates in Excel), then it should work.

**jeph_oliver** · 01-20-2015, 03:08 PM

Oh wow. Thank you so much. I have two more favors to ask of you if you're up to them?!

**mcmahobt** · 01-20-2015, 03:11 PM

Post them in this thread and I'll see if I can help.

**jeph_oliver** · 01-20-2015, 03:20 PM

Could you possibly come up with two formulas? The first is to convert the 4 days and 8 hours into strictly hours. Also, if you can have the minutes show up, so it'd end up looking like "104 hours and 25 minutes." The second is to convert the previous hours into all minutes. So it'd end up looking like "6265 minutes." I am sorry to ask this much of you but I really appreciate it!

**mcmahobt** · 01-20-2015, 03:44 PM

Are you sure it should be 104 hours in between, and not 114?

**mcmahobt** · 01-20-2015, 03:50 PM

Assuming it should be 114, see the attached workbook for additions. I went ahead and recreated B4 as a formula as well.

**jeph_oliver** · 01-20-2015, 03:50 PM

4 daysx24 hours=96 hours+8 hours=104.... Unless I'm missing something.

**jeph_oliver** · 01-20-2015, 03:55 PM

Also, I copied the formula and changed the cells to the ones that should be the one and I'm getting "#VALUE!" in both of the cells.

**mcmahobt** · 01-20-2015, 03:56 PM

I'm not sure where you are getting +8 hours from, but if you think about the progression:

Start time is at 18:30 - 6 hours AHEAD of the finish time
Finish time is at 1255 - Already 1255 (almost 13) hours into the new day (which isn't counted in the day total since it does not equal a full 24 hours)

Therefore you add on the 12 hours, + the 6 hour lag, to give you 18 hours total, along with the 4 days (and 25 minutes in change).

**mcmahobt** · 01-20-2015, 03:57 PM

As to the copy/paste error, make sure the cell references to the dates are still correct.

**jeph_oliver** · 01-20-2015, 04:06 PM

Wow, this is why I need this spreadsheet! And everything is the same before I do tried to use that formula! My cells are A9 and A1, however, I doubt that factors anything after I change it in the formula. Help?

**mcmahobt** · 01-20-2015, 04:11 PM

Can you post a sample workbook to work with?

**jeph_oliver** · 01-20-2015, 04:18 PM

If you want, I can send you my workbook if you'd like to fine tune it?

**mcmahobt** · 01-20-2015, 04:21 PM

You can attach the workbook to this thread directly so that others can work on it as well. Select Go Advanced below, then Manage Attachments to upload a sample workbook. Remember to desensitize data.

**mcmahobt** · 01-20-2015, 04:46 PM

What are in your A1 and A9 cells? Did you see the addition of the workbook in the previous post?

**jeph_oliver** · 01-20-2015, 05:03 PM

I merged and centered a few rows of cells to make the spreadsheet bigger and the lettering bigger. So I have about 8 cells merged.

**mcmahobt** · 01-20-2015, 05:20 PM

If that is the case, I recommend you start fresh, get the formulas working to your liking, then worry about formatting.

**jeph_oliver** · 01-20-2015, 05:22 PM

Woooooow. I had added another column of cells, which ultimately changed the cells for the formula. What a day! Thank you so much mcmahobt for all your help! Adding reputation right now!

**jeph_oliver** · 02-27-2015, 12:54 PM

So what I'm looking for is kind of the same thing but with the date and time section in different cells. Attached is an exampled of what I'm looking for. In column 4 is some dummy dates and times to illustrate what I'd need to find difference in. Once again, if you could help me I'd be very appreciative.

**mcmahobt** · 02-27-2015, 12:59 PM

Your example needs to be more fleshed-out, with better details. What is 48 HR Time? What is 0 HR Date? What is Pre Date and Pre Time?

**jeph_oliver** · 02-27-2015, 01:02 PM

Those specifics are to my job so I wasn't going too detailed on purpose.

**mcmahobt** · 02-27-2015, 01:04 PM

That makes sense, but then maybe you could alter the data and put in dummy times; or rather, explain what the column headings are in terms of significance?

**jeph_oliver** · 02-27-2015, 03:00 PM

The column headings are just time points and terms we have. Pre is predose and 48 hours if two days after that. So I'm wanting to get a calculator that will verify the length of time between the predose and 48 hours. The 0 hr and second 48 hour column is the same. But I need to know the difference between each column. So pre-48=, 48-0=, and 0-48(2nd one) equals. Is this making any more sense? Sorry, I'm not the best at explaining this.

**mcmahobt** · 02-27-2015, 03:13 PM

I apologize, but this is really not clearing anything up for me.

Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Re: Day into Hours into Minutes Calculator Spreadsheet

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions