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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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

ADVERTISEMENT

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

ADVERTISEMENT

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,137,200
Messages
5,680,125
Members
419,884
Latest member
james888sa

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