Formula is counting cell if nothing is entered

I have made a timesheet that will automatically round up to the nearest
quarter hour, but if no time is entered the round column stays as 12:00 am
and calcualtes as such. The first col. has the actual time then the second
col. has the formula =round(E12*24/0.25,0)*0.25/24 which works to round the
time, but, as I said, if nothing is entered in the time, the round col. shows
12:00 am.

Hi

Try one of the following modifications to your formula:

=IF(E12="","",ROUND(E12*24/0.25,0)*0.25/24)
=IF(ISBLANK(E12),"",ROUND(E12*24/0.25,0)*0.25/24)

Regards,
B. R. Ramachandran


"Jennifer" wrote:

> I have made a timesheet that will automatically round up to the nearest
> quarter hour, but if no time is entered the round column stays as 12:00 am
> and calcualtes as such. The first col. has the actual time then the second
> col. has the formula =round(E12*24/0.25,0)*0.25/24 which works to round the
> time, but, as I said, if nothing is entered in the time, the round col. shows
> 12:00 am.

I have tried both of those before and it comes back with a "Incorrect time
format" box and indicates to cancel or retry...

"B. R.Ramachandran" wrote:

> Hi
>
> Try one of the following modifications to your formula:
>
> =IF(E12="","",ROUND(E12*24/0.25,0)*0.25/24)
> =IF(ISBLANK(E12),"",ROUND(E12*24/0.25,0)*0.25/24)
>
> Regards,
> B. R. Ramachandran
>
>
> "Jennifer" wrote:
>
> > I have made a timesheet that will automatically round up to the nearest
> > quarter hour, but if no time is entered the round column stays as 12:00 am
> > and calcualtes as such. The first col. has the actual time then the second
> > col. has the formula =round(E12*24/0.25,0)*0.25/24 which works to round the
> > time, but, as I said, if nothing is entered in the time, the round col. shows
> > 12:00 am.

Jennifer

Trap the error in your formula.

=IF(E12="","",ROUND(E12*24/0.25,0)*0.25/24)

If E12 is blank then nothing, otherwise do the calculation.


Gord Dibben Excel MVP

On Wed, 14 Dec 2005 12:16:39 -0800, "Jennifer"
<[email protected]> wrote:

>I have made a timesheet that will automatically round up to the nearest
>quarter hour, but if no time is entered the round column stays as 12:00 am
>and calcualtes as such. The first col. has the actual time then the second
>col. has the formula =round(E12*24/0.25,0)*0.25/24 which works to round the
>time, but, as I said, if nothing is entered in the time, the round col. shows
>12:00 am.

Hi,

I did try out the formulas before suggesting them to you. They do work.
Make sure that the cell(s) containing the formula is(are) formatted for
"Time" as "1:30 PM".

Regards,
B. R. Ramachandran

"Jennifer" wrote:

> I have tried both of those before and it comes back with a "Incorrect time
> format" box and indicates to cancel or retry...
>
> "B. R.Ramachandran" wrote:
>
> > Hi
> >
> > Try one of the following modifications to your formula:
> >
> > =IF(E12="","",ROUND(E12*24/0.25,0)*0.25/24)
> > =IF(ISBLANK(E12),"",ROUND(E12*24/0.25,0)*0.25/24)
> >
> > Regards,
> > B. R. Ramachandran
> >
> >
> > "Jennifer" wrote:
> >
> > > I have made a timesheet that will automatically round up to the nearest
> > > quarter hour, but if no time is entered the round column stays as 12:00 am
> > > and calcualtes as such. The first col. has the actual time then the second
> > > col. has the formula =round(E12*24/0.25,0)*0.25/24 which works to round the
> > > time, but, as I said, if nothing is entered in the time, the round col. shows
> > > 12:00 am.