# Detracting Two Times In Hh:mm:ss Format And Dividing?

Jan 24, 2014

I'm not sure if it's just been a long week but I can't get get the following to work in a sum formula.

In the example below I want to deduct B1 from A1 and then Divide by C1. The result should be 0.57 but I can't get to it within 1 formula.

9:49:12 - 6:18:46 = 3:30:26 / 2 = 0.57!!! Not sure if it's because I'm using unfriendly hh:mm:ss format but I need to.

A1 - 9:49:12
B1 - 6:18:46
C1 - 2

## Converting Times Stored In Date Format To Number Format?

Mar 31, 2014

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.

## Average Of Times Which Are In Dd:hh:mm Format

Apr 3, 2008

How can you find the average of times?

I have times like
00:01:00
00:00:15
00:00:08
All are in dd:hh:mm format
I need to find their average which shld be 00:00:28 (approx 27.6 mins)
When I do average(A1:C1) its giving me div/0 error.

## Change Format Of Dates And Times?

Jul 21, 2014

I have dates (column E) and times (column F) currently in the format mm/dd/yyyy and hh:mm .

However, my formula in excel deals with dates and times in the format mmddyyyy and hhmm. How do I change the values of the dates to exclude the "/" and the times to exclude the ":" operators?

I want to change the actual value of the cell too. Basically, using custom format mmddyyyy in the date column will not work (because the true value of the date will still yield mm/dd/yyyy.

## Copy Range X Times & Paste & Format

Oct 9, 2007

I have a range of cells that is 10 rows high. I want to copy and paste this range 11 times while identifying each of the 11 copies of the range, such as; 02,03,04...12.

Please see attachment for an example of what I am trying to do.

Does anyone have an idea of how this might be done in VB?

## Replicate Table Format Multiple Times

Feb 29, 2008

I have a single sheet broken into multiple sections of 10 rows (see attached). The first 5 rows of the section are formatted and I need to Insert 5 rows underneath and format them the same. I can't work out how to do this for the different sections (ie: add 5 rows to section 1, then section 2 then 3). These functions will be on separate buttons to use multiple times. I would have used multiple sheets but the project calls for a single sheet representation.

## Count Number Of Times Conditional Format Conditions Met?

Apr 22, 2014

I have used Formula to ID cells containing a formula to flag cells red with conditional formatting in a list that do not have formula.

I need a single cell to change colour if any of the cells in the list do not have a formula. e.g. the subtotal in the top row could go red so the user can scroll down to find individual red cells.

Is there a way to count the red cells without formula in the list - Conditionally formatted cells are hard to count. I don't want to use a vba script to do it as the user probably won't run it and auto run slow calculations down.

Is there an array formula that can return true or false if not(CellHasFormula) is true anywhere in the list?

countif(AH1:AH2976,CellHasFormula) doesn't work.

## Auto-populating Starting Times From 1 Format To The Other -VBA Required

Jun 9, 2006

If you look at schedule.jpg you will see a how our work schedules are formatted.

Then if you look at schedule2.jpg, you will see what I wish to convert it to.

Now I'm looking for a way to search by the name of our employee (2 seperate sheets) and then by the time which corresponds to the first "1" in the chart under that name and the last "1" in the chart under that name.

So once I fill out our schedule.jpg, it will auto-populate the times on schedule2.jpg.

Is this even possible?

If necessary, I will be able to put both schedule and schedule 2 on one sheet.

On schedule.jpg time starts at 7:00 - Column C
and ends at 8:00 - Column AC

## Join 2 Times In Cell & Retain Time Format

Aug 23, 2007

I'm usually able to find my answer but I couldn't find anything to match what I need. Anyway... this is either really simple or impossible to do. I have two cells which have times in each. Say A1 has 5:00 PM and A2 has 10:00 PM. I'm just trying to make it so one cell will say:

From 5:00 PM to 10:00 PM... So I tried this ="From " & A1 & " to " & A2

But as I'm sure you already know that yields this result:

From 0.708333333333335 to 0.916666666666668

## Count IF And IF Then Subtract Two Times In 24 Hr Format To Recieve Time On Task

Jul 9, 2008

I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500.

## Dividing A Time Value

Jan 29, 2009

Need to divide a cell containing a time value (hrs:mins:secs). My timesheets calculates the number of hours on a job and I want to divide this total by the number of components made.

## Dividing And Returning Different Value Than Zero

Jun 24, 2014

How to do the following?

When dividing two cells and the answer is zero then I want to return a different value. I've tried the following with no luck:

=IF(ISERR(\$D20),"*",\$G20/\$G\$22*100)

or

=IF(\$H19,\$G19/\$G\$22*100,"-")

=if a1/b1 = 0 then "*", else return actual value

(N)(%)
64 13
82 16
82 16
2 0 --> change this to * or -
264 52
13 3
507(total)

## Dividing Numbers Evenly

Apr 6, 2009

I have a total at the bottom of my spreadsheet and I want to distribute that total equaly to the missing columns for each month and each product highlighted in green in the spreadsheet. What formula do I need to use to be able to do that?

At the end I want the numbers in each month to total the total at the bottom.

## Dividing Time By Numbers

Sep 6, 2006

I've found how to present time exceeding a minute in seconds ([m]:ss.00), but I need to formulate items per second. Doing simply CellItem / CellTime won't work. I've tried Minute(CellTime)*60+Second(CellTime), but this leaves out the hundreds of a second.

## Macro Loop – Find Specific Times, Extract Date, Average Values Of Times

Feb 23, 2010

In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.

Here’s a quick description:

Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible

1:45:

If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....

## Excel Formula To Calculate Difference In Times But Only Between Specific Times

Apr 8, 2014

I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:

E3 provides the start time of 4:00
H3 provides an end time of 15:30

If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.

## Dividing Column In Equal Parts?

Sep 15, 2014

Dividing column in equal parts divide a column in groups with the same rows and the same total sum? For example, I have the column A as following:

8,942

2,807

2,568

5,818

5,818

596

8,942

4,390

1,607

890

8,772

1,103

The total number of rows is 12 and total amount is 52,254

I want to divide the column in three equal parts with the same total. So each group must contain 4 rows with the total sum 17,418

## Dividing A List Into Equal Parts?

May 23, 2006

Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups.

Have attached an example.

## Dividing Multiple Arguments In Same Cell

Jul 26, 2014

V = 100 x [E /(IY + YP)] x Sqr. [((R + G)/2 / (IY + YP + F)]
V = 100 x [6.10 /(5.60)] x Sqr. [((10.2+2)/2 / (5.60 + 2.1]
V = 108.93 x Sqr (.79)
V = \$96.89 / share as of 7/18/2014

E = Earnings in \$ / share 6.1 (b9)
IY = Interest rate in % 5.6 (b10)
R = Profitability Factor in % (internal growth rate)10.2 (b11)
G = Earnings Growth in % / yr. 2 (b12)
F = Inflation Rate in % / yr. 2.1 (b13)

Here's what I have, result should be \$96.89, I need to tell excel to take square root of everything to the right not just (B11+ B12)

=100*(+B9/B10)*SQRT(B11+B12)/2/(B10+B13)

## Dividing Number With Hours And Minutes?

Feb 10, 2014

Cell A1 contains 2, B1=220077:23

In C1 I created a function = (2*1000)/B1, I get error. I want the answer 2000/220077h23m = 0.009087713

## Diagonally Dividing Individual Cell

Nov 5, 2008

how could divide one single cell in diagonal direction ?? without using line from drawing tool.

## Dividing Letters And Numbers String

Jan 25, 2009

A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.

Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2

What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:

xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on

Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?

## Sumproduct And Dividing With Blank Rows

Apr 9, 2009

I am trying to divide a range that has blanks in. The range is the result of a webquery and taking the blanks out would be time consuming at best.

Conveniently (potentially) the blank row is every other row in the range.

## Dividing Time In Excel Different Formats

Mar 13, 2013

I am attempting to calculate % utilization. I have number of minutes used formatted in [mm]:ss and number of minutes available formatted as a number. When I divide available by minutes used I get a percentage that is off.

So I have Available 33600 minutes available 4901:20 minutes used.

## Dividing A Number By 12 With Respect To Rounding

May 23, 2007

Suppose I have the following plan number (24) and I wan't to spread it across 12 columns, as evenly as possible but with respect to rounding.
Easy enough.. Each column receives 2...

Or say the figure was 24.6 and i wanted to round to 2 places...
Easy enough... Each column receives 2.05...

Or say the figure was 24.7 and i wanted to round to 2 places...
Easy enough... Each column receives 2.05... But one would be 2.06!

How about the number is 1 and say, the rounding is 0.1?
Ten of the columns would be .1 while two others (arbitrarily) would be 0.

## Dividing Data Compiled On One Worksheet To Others

Apr 15, 2009

In trying to solve my dilema I searched some older threads and found some code written by Peter SSs. I altered it to fit my situation ( or so I thought) because all I get is a compile error. I am not sure why. The data is lengnty but the sorting item is the provider name. The information for each provider should then post to the sheet with his name.

Here is the
Sub test()
Dim lr As Long
Dim ws As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("Audit Results").Range("F" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets

## Dividing To Get A Result Of Whole Numbers Preset

Dec 11, 2009

Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.

Windows XP
Excel 2003

## Dividing A List Into Equal Parts.

May 23, 2006

Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups. Have attached an example.

## Dividing Entire Range By A Number

Jul 10, 2006

I have a large worksheet that I need to make into a quarter of what it is now. Each of the numbers is just inputted, so I was thinking there is a way to divide it all by four.

## #DIV/0! Dividing Multiple Countif Functions Into Value

Jul 19, 2007

I have this formula

= COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")

that returns the number 2 (which is correct). However, if I precede it with

AZ6/COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")

it returns a DIV/0 error, even though AZ6 has a value of 24.

Surely 24/2 would return a value of 12? NB AZ6 cell value is derived from the result of a formula.