Trying to use IF and TODAY to auto-populate cells in the future.

Deadagain

New Member
Joined
Nov 21, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Trying to use IF and TODAY to auto-populate cells in the future. I have tried =IF(TODAY()=M7+K7-O7,Q7,"") but it does not work.

COLUMN C IS POPULATED BY A SEPARATE SHEET IN THE SAME WORKBOOK
COLUMN E IS THE START OF THEIR 12 MONTH HOLIDAY PERIOD i.e. FRED BLOGS PERIOD IS FROM 01/05/2020 TO 30/04/2021
COLUMN G IS AUTOMATICALLY POPULATED BY A SEPARATE SHEET IN THE SAME WORKBOOK
COLUMN I SHOWS THE CURRENT + OR - BALANCE OF THEIR ETITLEMENT ANY DEFICIT IS TAKEN FROM NEXT YEARS ENTITLEMENT AND ANY SURPLUS IS ADDED TO THEIR NEXT YEAR ENTITLEMENT
COLUMN K.THIS NEEDS TO BE POPULATED WHEN THE DATE IN COLUM M IS REACHED, POPULATION COMES FROM COLUMN C WHICH IS AUTOMATICALLY UPDATED FROM ANOTHER SHEET
COLUMN K IS WHERE THE PROBLEM STARTS! WE USE A 2 YEAR CALANDER TO ALLOW A FULL 12 MOTH ENTRY FOR EACH INDIVIDUAL AS THEY ALL HAVE DIFFERENT HOLIDAY START AND END PERIODS. THIS ACTION SHOULD ONLY BE PERFORMED ONCE.
COLUMN M SHOWS THE START OF THEIR NEXT HOLIDAY ENTITLEMENT PERIOD, THIS IS AUTOMATICALLY POPULATED.
COLUMN O IS AUTOMATICALLY POPULATED BY A SEPARATE SHEET IN THE SAME WORKBOOK
COLUMN Q SHOWS THEIR REMAINING BALANCE FOR THE 2021 HOLIDAY ENTITLEMENT PERIOD. AFTER THE INITIAL POPULATION, THIS SHOULD ONLY BE UPDATED BY COLUMN O
COLUMN Q NEEDS TO BE POPULATED ON THE 1ST DAY OF THEIR NEW ENTITLEMENT PERIOD INCLUDING AMENDMENTS BASED ON THEIR BALANCE ON THE LAST DAY OF THEIR 2020 ENTITLEMENT PERIOD (TAKEN FROM COLUMN I)
THEIR BALANCE AND BOOKED TIME IS PICKED UP BY THE DASHBOARD AT THE FRONT OF THE EXISTING WORKBOOK

I HAVE TRIED USING AN IF and TODAY FORMULA TO ACHIEVE THE REQUIRED FIGURES IN COLUMNS K AND Q BUT THIS IS PROOVING TO BEYOND MY CAPABILITIES WITH EXCEL .MY ATTEMPT WAS =IF(TODAY()=M7+K7-O7,Q7,""). IT ALSO LEAD TO QUESTIONS I COULD NOT WORK OUT :-
WOULD AN IF AND TODAY FORMULA RESULT IN THEIR ENTITLEMENT AND/OR BALANCE BEING CONTINUALLY UPDATED ONCE THE DESIRED DATE IS REACHED? i.e. Would Fred Bloggs be given 32 days for the 2021 period on 01/05/2021 and again for every other day passed 01/05/2021?
HOW DO I PREVENT THE ACTION IN COLUMNS K AND Q BEING REPEATED MORE THAN ONCE?
WOULD IT BE POSSIBLE FOR COLUMN Q, ONCE IT HAS BEEN POPULATED, TO ONLY UPDATE FROM THE CHANGES IN COLUMN O

IF and Toady.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1
2NAME2020 HOLIDAYHOLIDAYHOLIDAYS TAKEN2020 BALANCE2021 HOLIDAYHOLIDAYHOLIDAYS TAKEN2021 BALANCE
3ENTITLEMENTSTART DATEOR BOOKEDREMAININGENTITLEMENTSTART DATEOR BOOKEDREMAINING
4
5FRED BLOGS3201/05/202035-301/05/2021
6BILL BATES2701/01/2020111601/01/2021
7JEN CLOSE2701/06/202002701/06/2021
8MONICA FAR3201/11/202041-901/11/2021
9SALLY NOWHERE2401/05/202019501/05/2021
10IAN TEMPEST3201/01/202031101/01/2021
11FOOT BALLER2701/06/202029-201/06/2021
12CHAIN SMOKER3201/03/202033-101/03/2021
13BEER DRINKER2401/09/202051901/09/2021
14ANN OTHER3201/02/2020161601/02/2021
15
Sheet1
Cell Formulas
RangeFormula
I5:I14I5=C5-G5
M5:M14M5=DATE(YEAR(E5)+1,MONTH(E5),DAY(E5))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I6:I14Cell Value>=1textNO
I6:I14Cell Value<0textNO
I5Cell Value>=1textNO
I5Cell Value<0textNO
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343
Welcome to the MrExcel Message Board.

This is to inform you that I have updated your post to make it in proper structure as @Fluff explained in the test post you sent.

Please follow these basic rules to make your post more readable next time. It will help to receive quicker responses, and potential answers.
  1. Do not write the descriptions and your question in the worksheet to be converted by XL2BB. It will always create the unreadable look with wider columns as you originally pasted, and also tested in the test area.
  2. Keep the question title shorter instead of asking questions in the title consists of multiple sentences. Just look at the current title I edited. It is the all info needed in the title.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,822
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top