Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Mifty
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Anne Troy
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells

=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" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Mifty
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells

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" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.excel.misc
Mifty
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells

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" wrote in message
...
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




  #5   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells


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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Mifty
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells

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


  #7   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells


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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Mifty
 
Posts: n/a
Default Formula to calculate number of days & ignore blank cells

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
How can I get excel to ignore formula in dependant cells... Ted Excel Worksheet Functions 4 November 21st 05 11:03 PM
formula to calculate future date from date in cell plus days Chicesq Excel Worksheet Functions 8 November 3rd 05 01:25 PM
formula to calculate # of days between dates, excluding holidays abs2299 Excel Discussion (Misc queries) 8 March 3rd 05 03:21 AM


All times are GMT +1. The time now is 04:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"