May 08 2017
04:26 AM
- last edited on
Jul 25 2018
09:35 AM
by
TechCommunityAP
May 08 2017
04:26 AM
- last edited on
Jul 25 2018
09:35 AM
by
TechCommunityAP
I have a current formula -
=ROUNDUP(($A$1-J18)/7,0)
This tells me how many weeks have past from one date to another. What I want to add is that if another cell has a date in it that has passed then this sum should just read 0.
Please help me you lovely lot.
Thanks
May 08 2017 06:40 AM
May 08 2017 06:59 AM
Yes, that's no clear what is required. For example that could be
If <date in J18> is between (<date in another cell> and $A$1) then <number of weeks> else 0
May 08 2017 08:14 AM
Ok so I have to know how many weeks have passed.
One column is called 'HIRED ON DATE' the other column is called 'HIRED OFF DATE'. I then need a column that says 'WEEKS HIRED TO DATE'. It needs to calculate the amount of weeks from HIRED ON DATE until HIRED OFF DATE or if no date is entered into HIRED OFF then it needs to use TODAYS date.
Thanks in advance x
May 08 2017 08:21 AM
SolutionWhen it's something like this
=WEEKNUM(IF(ISBLANK(<hired off date>),TODAY(), <hired off date>)) - WEEKNUM(<hired on date>)
Perhaps plus 1 depends on how do you calculte not full week, as zero or as 1.
May 08 2017 01:17 PM
Sergi I couldnt get that to work. What is the - you have in the formula before the second WEEKNUM?
=WEEKNUM(IF(ISBLANK(<hired off date>),TODAY(), <hired off date>)) -
WEEKNUM(<hired on date>)
May 08 2017 01:48 PM
Hi Zoe,
Let split on two parts. First one returns fired day or today if fired day is empty, assuming you have them in column B
=IF(ISBLANK(B2),TODAY(),B2)
If hiring days in column A when number of weeks between these two days will be
=ROUNDUP((IF(ISBLANK(B2),TODAY(),B2)-A2)/7,0)
I'm sorry for previous formula, weeknum returns the week number within the year, it'l be simply to use your ROUNDUP
May 09 2017 06:08 AM
Hi Zoe Jeffery,
Good day,
It is my pleasure give some suggestion to your problem.
Please try to use below formula. I am not sure how mutch it may work respect to your requirement.
Please find attached work sheet and study.
=IF(ROUNDUP(($A$36-B39)/7,0)>$C$36,"0",$A$36-B39)
Regards,
Manoj P.
Jan 13 2020 04:03 PM
Hi, I saw your reply to a formula question similar to what i am trying to accomplish. I have the following formula that works mostly how i want it to, however i need the "G31/75" portion to round up.
=IF($G$31<=1500,($G$31/75),0)
Can you help me?
Thank you in advance
Jan 14 2020 01:19 AM
@JamesWise , why don't you use ROUNDUP() function?
May 08 2017 08:21 AM
SolutionWhen it's something like this
=WEEKNUM(IF(ISBLANK(<hired off date>),TODAY(), <hired off date>)) - WEEKNUM(<hired on date>)
Perhaps plus 1 depends on how do you calculte not full week, as zero or as 1.