Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
How can I get excel to ignore formula in dependant cells... | Excel Worksheet Functions | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) |