[Excel]Reset/replace seconds to zero

Last update on November 4, 2009 09:05 AM by aakai1056
Published by aakai1056

[Excel]Reset/replace seconds to zero





Issue


I have an Excel 2007 file which has in sheet1 the time people connects to the system with the following format: 11:55:31 AM and I want to reset all the seconds to zero in sheet2, considering that I have column with all the time start from 0:00:00 to 23:59:00 (seconds always set to zero) and I need to count for each time lap how many people connects to the system depending on the time which in sheet1

Solution


Suppose you have data in Sheet1!CellA1:A36. Now in Sheet two Column A write 0:00:00 to 23:59:00 values ....... now in sheet2!B1 write this formula 

 =COUNTIF(Sheet1!$A$1:$A$100,"<="&A2)-COUNTIF(Sheet1!$A$1:$A$100,"<="&A1)

Notes


Thanks to mubashir aziz, for his solution on the forum.
Best answers for « Reset/replace seconds to zero » in :
How to restore deleted files ShowHow to restore deleted files Download a recovery tool There are easy and effective recovery tools available for download on the web and some have the major advantage of being free. Zero Assumption Recovery Download Link: http://www.z-a...
Replacing the battery of your motherboard (CMOS battery) ShowReplacing the battery of your motherboard (CMOS battery) Topics Symptoms Replacement sequence Saving the BIOS data Identify the CMOS battery Removing the discharged battery Installing the new battery Verify the BIOS data and resetting...
How to Resize a Partition using Gparted on Linux? ShowHow to Resize a Partition using Gparted on Linux? You have a partition and you want to enlarge or reduce it without losing data? It is possible and relatively easy with GParted, a LiveCD, ie a bootable CD, providing an interface...
Download Advanced Find&Replace for Excel ShowDescription This application is designed by Add-in Express LTD. Advanced Find & Replace is an add-on for Microsoft Excel. It allows you to run multiple searches for values, formulas and comments. The search results are displayed in a floating...
Download ResHack ShowResHack ( Resource Hacker) is a freeware which allows to view, modify, rame, to delete or to extract resources in executable files (.exe, .ocx, .dll, .res, etc.). It has an internal compiler/decompiler which allows to : ยท Read cursors, icones,...
Download Resize My Pictures ShowWhen you broadcast pictures on Internet, that it is for sites Webs or blogs, it is always preferable to reduce their sizes. On the other hand, for impression or publications, pictures of big sizes are recommended. Resizemypicture.com is a tool...
Enterprise Resource Planning (ERP) - Integrated Management Softw ShowIntroduction to ERP ERP (in English Enterprise Resource Planning), also called Integrated Management Software (PGI), are applications whose purpose consists in coordinating all activities of a company (so-called vertical activities such as...
Resistance to change ShowResistance to change To be in a position to anticipate change, it is essential to understand the risk factors and in particular the causes that prevent change. Apart from individual causes, before focusing attention on a case by case basis,...
They need your help