software

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))),quot;quot;,(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

=IF(ISBLANK(A1),quot;quot;,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

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

Will try tomorrow and let you know.

Thanks for taking the time to answer
Cheers
--
Miftyquot;Anne Troyquot; wrote:

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

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

--
Miftyquot;Anne Troyquot; wrote:

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


You could try this....

=IF(A1=quot;quot;,quot;quot;,IF(B1=quot;quot;,TODAY(),B1)-A1 1)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=509507Hi there daddylonglegs,

tried that but get a 1 in cell C if A is blank. Any way around this?
--
Miftyquot;daddylonglegsquot; wrote:

gt;
gt; You could try this....
gt;
gt; =IF(A1=quot;quot;,quot;quot;,IF(B1=quot;quot;,TODAY(),B1)-A1 1)
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=509507
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=509507My mistake!!!!
Sorry it works perefectly
Many thanks to Daddylonglegs and Anne
Cheers

--
Miftyquot;daddylonglegsquot; wrote:

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