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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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