LinkBack URL
About LinkBacks
Need help deriving a formula for my business spreadsheet, I have a collum with Job start date and a collum with reports, I need a formula so that when an employee has been at my company for sixteen months.
Report Cell Formatting and Formula
Need help deriving a formula for my business spreadsheet, I have a collum with Job start date and a collum with reports, I need a formula so that when an employee has been at my company for sixteen months.
Last edited by PistolPete7; 05-06-2017 at 09:21 PM.
Post # 1 was CHANGED. The original question was (paraphrasing), "I need a report on each employee every six months, starting from their date of hire (Column A). On the day the report is due, I want Column (?) to show that date."
Hi Pete- If start date is in A2, this will only show for ONE DAY every 6 months:
=IF(TODAY()=EDATE($A2,6*INT((TODAY()-$A2)/182.5)),TODAY(),"")
The next day, it will be blank again. Is that really what you want?
I think it might be better to compare 2 columns, 'Report Due' and 'Last Report Completed'. If report due is greater, use conditional formatting to turn the cells RED or something. See the attached workbook for example.See Post #11 for Final version of this workbook. -Lee
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
Last edited by leelnich; 05-06-2017 at 10:53 PM.
Thats the right direction but it just needs to come up for these dates , from today if they have been at the company for six months, Do you think I could maybe do a conditional format?Originally Posted by leelnich
Hi Pete- If start date is in A2, this will only show for ONE DAY every 6 months:
=IF(TODAY()=EDATE($A2,6*INT((TODAY()-$A2)/182.5)),TODAY(),"")
The next day, it will be blank again. Is that really what you want?
I think it might be better to compare 2 columns, 'Report Due' and 'Last Report Completed'. If report due is greater, use conditional formatting to turn the cells RED or something. See the attached workbook for example.
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
Last edited by PistolPete7; 05-06-2017 at 03:09 AM.
I edited post #2 again after your last post, go there and upload the workbook.
It works, but I stupidly forgot it needs to filter the dates in intivals of six months, and when it is someones 6 month check up it needs to have "due" instead of a date. Thanks for helping though
Paste this in B2:
Also, note that our sytem date formats are different. Here we do mm/dd/yyyy, there you do dd/mm/yyyy.Formula:
Please Login or Register to view this content.
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
Last edited by leelnich; 05-06-2017 at 01:59 AM.
That worked partly, is there any way I could send you a screen shot of the document the c2 reference doesn't work with the spreadsheetPaste this in B2:
Also, note that our sytem date formats are different. Here we do mm/dd/yyyy, there you do dd/mm/yyyy.Formula:Please Login or Register to view this content.
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
refer bellow
Last edited by PistolPete7; 05-06-2017 at 02:25 AM.
This is the table format, I just need by any means necessary to have only dates that fall on sixteen months to show up like the columns to the left
Last edited by PistolPete7; 05-06-2017 at 09:45 PM.
The numbers shown in the 'Staff Reporting' column ARE dates. You just have to change the Cell Formatting/Number Format to the Date type, so Excel will display them correctly.
NOTE: I'm not seeing a 'Last Report Completed' column. Without it, you have no way to tell if the report is done, so TODAY() will always be greater than the last required report and Staff Reporting will ALWAYS show 'Report Due'.
Last edited by leelnich; 05-06-2017 at 04:28 AM.
OK, I adjusted the formula in the 'Staff reporting' column. It now shows either "Report Due" (HIGHLIGHTED) or the NEXT Reporting date.
I'm pretty sure this is the output you want, but it still depends on a second column to determine if the LAST Report was filed.
I don't think you can do this any other way.-Lee
Last edited by leelnich; 05-07-2017 at 09:16 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks