Hello all. I am looking for a DATE formula. I've tried so many formula iterations without success. Frustration level now 985....
Here's the scenario; I have 12 Worksheets (January through December) where Employees Payroll records are posted with formulas in other columns to calculate different government contributions based on their salary. On a separate Worksheet (named NIS184), which, once a month we are required to submit all employees contributions, a VBA routine posts the sheet name where the data is located to retrieve the sum of the salary for that month and year for each employee displayed in a cell. There are other cells that retrieve each contribution for that PayDate and the following PayDates. This month name (This is a text cell named NISConMth) can be changed by the user for any month. There is a From Date and To Date Cell. I am trying to automatically display the From date for the month shown. There is also a Year cell named NISConYr that retrieves the year from the last record Date located on the Month Sheet. All my formulas worked as I wanted but when I tested if a previous year was in the dataset I encountered an epic failure. All because there was a prior year and how the formulas searched through the records.
Shown below are entry dates (Paydates) for records. Each (Pay) date has 27 entry rows (1 per employee). End of month is for 5 Motnhly paid employee. The FROM date on the NIS184 Sheet needs to display 2022-08-05 which is the first (Pay) date of August 2022.
Given the month (text format and also the sheet name) and year (date format), how do I get earliest (Pay)date (Day) of the month and year to show in the FROM Cell? I'll include the sheets necessary to show what I'm talking about with callout notes explaining the data.
Note: XL2BB did not pick up the callout clouds and other items so here's a link to the file located on my Google Drive. The file does not have any macros and is clean.
Link to the Book1Help.xlsx file.
August 6, 2021
August 13, 2021
August 20, 2021
August 27, 2021
August 31, 2021
September 3, 2021
August 5, 2022
August 12, 2022
August 19, 2022
August 26, 2022
August 31, 2022
September 2, 2022
Thanks for your indulgence.
Derick
Here's the scenario; I have 12 Worksheets (January through December) where Employees Payroll records are posted with formulas in other columns to calculate different government contributions based on their salary. On a separate Worksheet (named NIS184), which, once a month we are required to submit all employees contributions, a VBA routine posts the sheet name where the data is located to retrieve the sum of the salary for that month and year for each employee displayed in a cell. There are other cells that retrieve each contribution for that PayDate and the following PayDates. This month name (This is a text cell named NISConMth) can be changed by the user for any month. There is a From Date and To Date Cell. I am trying to automatically display the From date for the month shown. There is also a Year cell named NISConYr that retrieves the year from the last record Date located on the Month Sheet. All my formulas worked as I wanted but when I tested if a previous year was in the dataset I encountered an epic failure. All because there was a prior year and how the formulas searched through the records.
Shown below are entry dates (Paydates) for records. Each (Pay) date has 27 entry rows (1 per employee). End of month is for 5 Motnhly paid employee. The FROM date on the NIS184 Sheet needs to display 2022-08-05 which is the first (Pay) date of August 2022.
Given the month (text format and also the sheet name) and year (date format), how do I get earliest (Pay)date (Day) of the month and year to show in the FROM Cell? I'll include the sheets necessary to show what I'm talking about with callout notes explaining the data.
Note: XL2BB did not pick up the callout clouds and other items so here's a link to the file located on my Google Drive. The file does not have any macros and is clean.
Link to the Book1Help.xlsx file.
August 6, 2021
August 13, 2021
August 20, 2021
August 27, 2021
August 31, 2021
September 3, 2021
August 5, 2022
August 12, 2022
August 19, 2022
August 26, 2022
August 31, 2022
September 2, 2022
Thanks for your indulgence.
Derick
Last edited:
