Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Help
I need to import data from another application (RM airline managment) which i do using the .csv file. It imports fine, but the data shows the departure times for aircraft in 0:00 format, which excel sees as h:mm. The problem i have is when an aircraft departs early the figure is -0:03. The data i have is a mixture of positive times (0:07) and negative times (-0:03) when i use the count function it doesnt count any negative times only the positive, unless i put in "" around the time (-"0:03") and using the 1904 date system. Any ideas on solutions? #was thinking if there is away of using the replace function to find all the cells containing the "-" and enter the "s around the time to allow the count function to work...? #or am i being stupid and is there a way to set up the count function to count the negative times as they are? Any help will be greatly appriciated Many thanks Sam -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=509343 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Help
depending what else is in the column you could try counta function instead of count. This will count text as well as numbers -0:07 is treated as text as excel does not accept negatives times. This can be a pain sometimes! Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=509343 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Help
Can you get COUNTA to count a range of data? for example -9:99 to 0:03? if so what formula would i use?? Also there is more data in the spreadsheet than just these times, wouldnt COUNTA count everything in the sheet not just the column containing the departure difference times?? Brgds -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=509343 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Help
Maybe you could use a little macro to fix those text values.
Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myRng = Selection For Each myCell In myRng.Cells If myCell.Value Like "-##:##" _ Or myCell.Value Like "-#:##" Then With myCell .NumberFormat = "hh:mm" .Formula = "=-""" & Mid(myCell.Value, 2) & """" End With End If Next myCell End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Just select the range to adjust and run the macro. SamGB wrote: I need to import data from another application (RM airline managment) which i do using the .csv file. It imports fine, but the data shows the departure times for aircraft in 0:00 format, which excel sees as h:mm. The problem i have is when an aircraft departs early the figure is -0:03. The data i have is a mixture of positive times (0:07) and negative times (-0:03) when i use the count function it doesnt count any negative times only the positive, unless i put in "" around the time (-"0:03") and using the 1904 date system. Any ideas on solutions? #was thinking if there is away of using the replace function to find all the cells containing the "-" and enter the "s around the time to allow the count function to work...? #or am i being stupid and is there a way to set up the count function to count the negative times as they are? Any help will be greatly appriciated Many thanks Sam -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=509343 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Help
=counta() will count any range you give it. So if you give it that isolated
range, it'll just count those cells. I don't think =counta() will count ranges when you're mixing text with numbers--well, I'm not sure I'd trust it without extensive testing. SamGB wrote: Can you get COUNTA to count a range of data? for example -9:99 to 0:03? if so what formula would i use?? Also there is more data in the spreadsheet than just these times, wouldnt COUNTA count everything in the sheet not just the column containing the departure difference times?? Brgds -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=509343 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |