Formula to calculate number of days & ignore blank cells

[Mifty]

Hi there,

I've set up a spreadsheet for someone else that calculates the number of
days worked by temporary employees. When I initially set it up, I thought I
just needed a column for start dates and a column for end dates, but the
person I have set it up for also wants a number of dates worked up to now
column.

I'm very new to this so realise that the way I have done it is probably
quite clumsy.

My original formula in C (days worked) was
=IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end
date. I used isblank so that C would remain empty if A and B were blank.

My problem is how do I amend this formula so that if I added an extra column
(today's date) C(days worked) would say stay blank if A is empty if B(end
date) is empty calculate number of days worked so far but if B(end date) has
a date then calculate how many day worked altogether.

Hope this isn't total gobbledygook.

Many thanks
Mifty


--
Mifty

[Anne Troy]

=IF(ISBLANK(A1),"",IF(ISBLANK(B1),TODAY()-A1,B1-A1))
Try that.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Mifty" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> I've set up a spreadsheet for someone else that calculates the number of
> days worked by temporary employees. When I initially set it up, I thought
> I
> just needed a column for start dates and a column for end dates, but the
> person I have set it up for also wants a number of dates worked up to now
> column.
>
> I'm very new to this so realise that the way I have done it is probably
> quite clumsy.
>
> My original formula in C (days worked) was
> =IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end
> date. I used isblank so that C would remain empty if A and B were blank.
>
> My problem is how do I amend this formula so that if I added an extra
> column
> (today's date) C(days worked) would say stay blank if A is empty if B(end
> date) is empty calculate number of days worked so far but if B(end date)
> has
> a date then calculate how many day worked altogether.
>
> Hope this isn't total gobbledygook.
>
> Many thanks
> Mifty
>
>
> --
> Mifty

[Mifty]

Hi Anne,

Will try tomorrow and let you know.

Thanks for taking the time to answer
Cheers
--
Mifty


"Anne Troy" wrote:

> =IF(ISBLANK(A1),"",IF(ISBLANK(B1),TODAY()-A1,B1-A1))
> Try that.
> ************
> Hope it helps!
> Anne Troy
> www.OfficeArticles.com
> Check out the NEWsgroup stats!
> Check out: www.ExcelUserConference.com
>
> "Mifty" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there,
> >
> > I've set up a spreadsheet for someone else that calculates the number of
> > days worked by temporary employees. When I initially set it up, I thought
> > I
> > just needed a column for start dates and a column for end dates, but the
> > person I have set it up for also wants a number of dates worked up to now
> > column.
> >
> > I'm very new to this so realise that the way I have done it is probably
> > quite clumsy.
> >
> > My original formula in C (days worked) was
> > =IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end
> > date. I used isblank so that C would remain empty if A and B were blank.
> >
> > My problem is how do I amend this formula so that if I added an extra
> > column
> > (today's date) C(days worked) would say stay blank if A is empty if B(end
> > date) is empty calculate number of days worked so far but if B(end date)
> > has
> > a date then calculate how many day worked altogether.
> >
> > Hope this isn't total gobbledygook.
> >
> > Many thanks
> > Mifty
> >
> >
> > --
> > Mifty
>
>
>

[Mifty]

Lovely thank you :-)

Now all I need is a way to calculate the difference between the two dates
that counts the 1st and last day

Any ideas? or should I start a new thread

Cheers

--
Mifty


"Anne Troy" wrote:

> =IF(ISBLANK(A1),"",IF(ISBLANK(B1),TODAY()-A1,B1-A1))
> Try that.
> ************
> Hope it helps!
> Anne Troy
> www.OfficeArticles.com
> Check out the NEWsgroup stats!
> Check out: www.ExcelUserConference.com
>
> "Mifty" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there,
> >
> > I've set up a spreadsheet for someone else that calculates the number of
> > days worked by temporary employees. When I initially set it up, I thought
> > I
> > just needed a column for start dates and a column for end dates, but the
> > person I have set it up for also wants a number of dates worked up to now
> > column.
> >
> > I'm very new to this so realise that the way I have done it is probably
> > quite clumsy.
> >
> > My original formula in C (days worked) was
> > =IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end
> > date. I used isblank so that C would remain empty if A and B were blank.
> >
> > My problem is how do I amend this formula so that if I added an extra
> > column
> > (today's date) C(days worked) would say stay blank if A is empty if B(end
> > date) is empty calculate number of days worked so far but if B(end date)
> > has
> > a date then calculate how many day worked altogether.
> >
> > Hope this isn't total gobbledygook.
> >
> > Many thanks
> > Mifty
> >
> >
> > --
> > Mifty
>
>
>

[daddylonglegs]

You could try this....

=IF(A1="","",IF(B1="",TODAY(),B1)-A1+1)

[Mifty]

Hi there daddylonglegs,

tried that but get a 1 in cell C if A is blank. Any way around this?
--
Mifty


"daddylonglegs" wrote:

>
> You could try this....
>
> =IF(A1="","",IF(B1="",TODAY(),B1)-A1+1)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=509507
>
>

[daddylonglegs]

If A1 is truly blank you can only get a blank using that formula

Are you using the formula exactly as I posted it or have you modified it?

...or does A1 have something in it - perhaps 0?

[Mifty]

My mistake!!!!
Sorry it works perefectly
Many thanks to Daddylonglegs and Anne
Cheers

--
Mifty


"daddylonglegs" wrote:

>
> If A1 is truly blank you can only get a blank using that formula
>
> Are you using the formula exactly as I posted it or have you modified
> it?
>
> ...or does A1 have something in it - perhaps 0?
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=509507
>
>

[Sazedul]

Hi
The difference between the two dates comes zero, I want there text as "Same Day" such as A1-A2=0 (Zero will show Same Day). If A1 & A2 both are blank then what will be the formula, please advise.

Thanks
Sazedul

[FDibbins]

Hi
The difference between the two dates comes zero, I want there text as "Same Day" such as A1-A2=0 (Zero will show Same Day). If A1 & A2 both are blank then what will be the formula, please advise.

Thanks
Sazedul

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[Sazedul]

Hi
The difference between the two dates comes zero, I want there text as "Same Day" such as A1-A2=0 (Zero will show Same Day). If A1 & A2 both are blank then what will be the formula, please advise.

Thanks
Sazedul

[AliGW]

Administrative Note:

Welcome to the forum.

Sorry, but your post does not comply with Rule #6 of our Forum RULES:

Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction, then send a private message to them asking for clarification.

All Participants:

Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.