zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
Trying to use mode on data with negatives times.

-0:11#value !
-0:06#value !
-0:03#value !
-0:05#value !
0.010:08
0.000:00
0.000:01

The #value is removing the seconds if present

formula [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]INT(CJ15*1440)/1440
I'm removing seconds as I thought it was affecting the mode function, but it in itself has the #value error.

So, do you need to condition times before mode will work with positive and negative times data set? [/FONT]


<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Before we can devise a solution we need to establish exactly what you have in your sheet.

How are you getting excel to accept the negative times?

Typically entry of negative dates and times is not accepted by excel unless you use some kind of workaround. Formulas that result in negative date or time values would show ######.

Are they formatted as text? Entered as decimal then formated as time using the 1904 date system? Or something else?
 
Upvote 0
Before we can devise a solution we need to establish exactly what you have in your sheet.

How are you getting excel to accept the negative times?

Typically entry of negative dates and times is not accepted by excel unless you use some kind of workaround. Formulas that result in negative date or time values would show ######.

Are they formatted as text? Entered as decimal then formated as time using the 1904 date system? Or something else?


using
time using the 1904 date system
 
Upvote 0
You might need to upload a workbook with the problem to a file share site so that we can see exactly what you're working with.

I've just tried =INT(A1*1440)/1440 and MODE() with negative times without any problems when using 1904 dates.

The only way that I can reproduce the #VALUE ! errors is by having text times in the left hand column. If this is what you have in your actual file then there would need to be something to establish whether the times are in [h]:mm or [m]:ss format.
 
Upvote 0
You might need to upload a workbook with the problem to a file share site so that we can see exactly what you're working with.

I've just tried =INT(A1*1440)/1440 and MODE() with negative times without any problems when using 1904 dates.

The only way that I can reproduce the #VALUE ! errors is by having text times in the left hand column. If this is what you have in your actual file then there would need to be something to establish whether the times are in [h]:mm or [m]:ss format.


Yes, figures do go left when negative signalng text correct? Format is of h:mm which I have cell format as, which I suppose will not actually represnt true "base" value, correct?
 
Upvote 0
Small sample of, paste values (copy ,paste) into new worksheet.

So , I assume because of - showing that negative time is seen as text, correct? Which then causes an issue for Mode?

Even using the data sample below I get #N/A using Mode. I notice you used [h]:mm does this hold significant value in what we are trying to achieve?

0.002488426
0.00255787
0.009259259
-0:18
-0:18
0.001956019
-0:03
-0:02
-0:11
0.001666667
-0:02
0.00693287
-0:15

<tbody>
</tbody>


ok does this help, when cell with negative time is clicked example -0:12 the formula tool bar shows that as is -0:12, but the positive shows as 12:12:00 AM.
 
Last edited:
Upvote 0
It would appear that this is correct.

IT can be confirmed by attempting to change the format using the dropdown in the middle of the 'Home' tab on the excel ribbon. If the example value is the same under all of the formats listed in the dropdown then it is text, if it varies between the different formats then it is numeric.

Try this in a helper column to coerce the times into numbers, then get the mode from the helper.

=SUBSTITUTE(CJ15,"-","")*IF(LEFT(CJ15,1)="-",-1,1)

I think that should work as long as you have 1904 dates selected in advanced options. I've got it working, but not tested thoroughly.

edit:-

[h]:mm or h:mm should make no difference, it only affects how the times over 24 hours are displayed.

MODE will show N/A if there is no mode (no value that appears more than once).
 
Last edited:
Upvote 0
It would appear that this is correct.

IT can be confirmed by attempting to change the format using the dropdown in the middle of the 'Home' tab on the excel ribbon. If the example value is the same under all of the formats listed in the dropdown then it is text, if it varies between the different formats then it is numeric.

Try this in a helper column to coerce the times into numbers, then get the mode from the helper.

=SUBSTITUTE(CJ15,"-","")*IF(LEFT(CJ15,1)="-",-1,1)

I think that should work as long as you have 1904 dates selected in advanced options. I've got it working, but not tested thoroughly.

edit:-

[h]:mm or h:mm should make no difference, it only affects how the times over 24 hours are displayed.

MODE will show N/A if there is no mode (no value that appears more than once).

Ok, #N/A explained thanks, and that is why I was looking at truncating the seconds as there would never be the same time with seconds involved, but many on the h:mm situation.

ok, off to try helper idea......
 
Upvote 0
Ok, #N/A explained thanks, and that is why I was looking at truncating the seconds as there would never be the same time with seconds involved, but many on the h:mm situation.

ok, off to try helper idea......

No like that formula.. #NA me error and yes all format show same when -, different when +
 
Upvote 0
ok I missed the row number in formula. SO the - times such as -0:00 show, but anything > then -0:01 show ################.
+ are good.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top