Macros for Date and Time

divuraj

Board Regular
Joined
Sep 28, 2006
Messages
75
Hi,

I have a worksheet with different values in time format for eg, 0:00:00, 1:01:11 etc. now my question is if my sheet has time in the format 0:00:00 then that needs to be displayed blank and the others should remain in the same format.

the purpose is the 0:00:00 should not be counted and the rest need to be counted. so that is the reason i need them to be blank.

how is this possible ? i am tying on this since long time. Pls help!!!

Thanks!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

smi123

Board Regular
Joined
Nov 7, 2005
Messages
67
Hi

If you are counting have you tried this formula

Where a1 might contain 0:00:00 and b1 contains 01:01:01 and c1 contains 12:01:10

=COUNTIF(A1:C1,"<>0")

The answer will be 2

Is this what you are after?
 

divuraj

Board Regular
Joined
Sep 28, 2006
Messages
75
This also helps !!

Thanks Sam for the prompt reply. but is there anyway we can display those "0:00:00" as blank , because we are not looking at Sales reps who had no call activity at all.

it will be better if we show them blank and take the reps who have handled customer calls on that day.

Help would be greatly appreciated.

Thanks,
Divya.
 

smi123

Board Regular
Joined
Nov 7, 2005
Messages
67
Select from Excel menu
[Tools] [Options] [View]

In windows options section near bottom of window tick off Zero Values

0's will not be displayed and countif formula still works
 

divuraj

Board Regular
Joined
Sep 28, 2006
Messages
75
Oh Great !!
This helped a lot...

Thanks a ton. this saves my manual work.

Divuraj.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Divya

If there are parts of the sheet where you need 0s displayed, then you might not want to use Sam's global option, in which case you can use a custom number format instead - something like:

Code:
[$-F400]h:mm:ss AM/PM;;

Richard
 

divuraj

Board Regular
Joined
Sep 28, 2006
Messages
75
Richard,

Thanks for the info.

When i try using this, the other cell values changes to a format for eg, like this : 1:01:11 AM but i need the rest of the cells in the 1:01:11 format. i tried taking off the AM/PM from the custom formula but it doesn't work.

Pls help !

Thanks,
Divuraj.
 

Forum statistics

Threads
1,171,685
Messages
5,876,889
Members
433,217
Latest member
Muhammad Tanzeel Ur Rehma

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
Top