Count Help

[SamGB]

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?
#OR is there away of getting all the negative times to appear as 0:00 as the function of the spread sheet is to get a percentage of on time departures (which includes early ones) departure <15mins and departure <60mins so if they defaulted to 0:00 the data will still be valid


Any help will be greatly appriciated
Many thanks
Sam

[Dav]

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

[SamGB]

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

[Dave Peterson]

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

[Dave Peterson]

=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