/u/Diddlydiddlydo1 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

Your XLOOKUP() in column AU is outputting zero, which you have conveniently hidden with cell formatting. This means it is not ="", thus your eDate returns zero, causing everything else to break.

/u/thebalancewithin - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

Are your values numerical? Try multiplying the first and second arguments of your match function to ensure they are excel-recognized numbers. You may also attempt to round them before matching in case of floating point error.

If your values are not numerical, try checking for superfluous characters in your cells. You may use =A1=B1 for two cells which appear the same to you, to confirm if excel also recognizes them as the same.

Have you got the show formulas option clicked? Formula tab, formula auditing section then the show formula option.

Check that your workbook calculates formulas automatically and hasn't switched to manually updating somehow. From memory, it's under File/Options.

I’d look at Ctrl+~ which toggles between displaying formulas in the worksheet or the results. This would leave your =SUM(blah:blah) formula as =SUM(blah:blah) in the cell. The ~ key is right below Esc, people often hit it accidentally and then scramble trying to ‘undo’ a display option.

If it’s that the values don’t change then look at your Calculate settings. If you hit F9 and everything calculates, then you’re likely set to Manual calc. This Calculation Options is toward the left in the Formula menu bar. The dropdown lets you select Automatic (what you want), Auto except for tables, and Manual. Once in Manual, hit F9 to force a recalc.

EDIT: to add that the Calc Options can be set by individual workbooks. If you’re sharing files someone else may have set a file to Manual, then you opened that file, then proceeded to open other files which dutifully carried over the Manual setting.

Check that your cells are not formatted as text. If they are, change them back to general and hit enter on the cell again.

This is the drop down on the home tab where you can change a cell from Currency or Accounting or Short Date etc.

Lotus compatibility settings? Huh.

OP- where are you getting your spreadsheets from?

Dear Sir or Madam,

I love u. It worked. thank you very much. Any idea how it happened?

Solution verified

Try the most basic formula, eg, =A1+B1, and see if that works. If it does then it seems to indicate that there's something wrong with the formula you're actually creating rather than the Excel sheet.

Might be nice to see your formula that does not work. Without it, how do we tell you how to fix it?

Whatever else you do when seeking help, DO NOT give clear examples. s/

=IF(C2=“Yes”,1,2)

Make sure the cell formatted correctly not as a text

I don't have Excel in front of me but from the ribbon it's something like:

Formulas > Calculation Options > Automatic

Okay not sure why it's exhibiting that behavior since you mentioned it's Number not Text. But if I had to fix this quickly, I would do a Find and Replace (Ctrl+H shortcut) - find "=" and replace with "=" would kind of make it reenter all the formulas.

Also check for any circular references.

Check if the format of the cells is text and if so, change it

Another trick is to show formulas (formulas menu), copy & paste into notepad, copy & paste back into Excel.

Try holding the control key and hitting Tilda.

CTRL + ~

Select any cell where formula is there. Right click, format cells, click General and then Ok. Then ctrl+F2

/u/JaFakeItTillYouJaMak - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

Put a ‘ in front of the formula. Hit enter. Change the format to general. Get rid of the ‘ in front of the formula. Hit enter. Don’t ask, but it works.

You just need to use only this formula in Conditional Formatting and remove anything what you have used, and when next pay period arrives just filp it. =DAY($C7)<16

=DAY($C7)>15

Watch the animation:

https://i.redd.it/vb7hjudd6vgf1.gif

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

|Fewer Letters|More Letters| |-------|---------|---| |DAY|Converts a serial number to a day of the month| |EOMONTH|Returns the serial number of the last day of the month before or after a specified number of months| |IF|Specifies a logical test to perform| |OR|Returns TRUE if any argument is TRUE| |TODAY|Returns the serial number of today's date|

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(5 acronyms in this thread; )^(the most compressed thread commented on today)^( has 42 acronyms.)
^([Thread #44622 for this sub, first seen 3rd Aug 2025, 20:32]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])

This post has been removed due to Rule 1 - Poor Post Title.

Please post with a title that clearly describes the issue.

The title of your post should be a clear summary of your issue. It should not be your supposed solution, or just a function mention, or a vague how to. A good title is generally summed up in a sentence from questions posed in your post.

Here's a long example and a short example of good posts.

Rules are enforced to promote high quality posts for the community and to ensure questions can be easily navigated and referenced for future use. See the Posting Guidelines for more details, and tips on how to make great posts.

To our users, please report poorly titled posts rather than answer them, they will be removed along with the answers.

Yeah, that would be a reasonable way to do it, I think

make a post, do not hijack a post

If you want to relate the conditional formatting to the current pay period then don't you need to reference today's date in the formula?

This should format dates outside the current period, assuming you are only showing the current month

=IF(DAY(TODAY())<16,DAY($C7)>15,DAY($C7)<16)

or to exclude any other dates, even outside the current month

=EOMONTH($C7,-1)+15*(DAY($C7)>15)<>EOMONTH(TODAY(),-1)+15*(DAY(TODAY())>15)

Пиши формулы двумя разными способами и сверяй результат. Например query и filter.

Если формула сложная, я пользуюсь конструкцией:

=arrayformula(let( isDebug; true;

out; "some formula"; debug; "debug data formula";

if(isDebug;debug;out) ))

В ней считаю основное, плюс в debug вывожу все промежуточные вычисления, комментарии и т.п. Это очень помогает в отладке и поиске проблем в релизной версии

At first, I'm usually just doing filtering or manual calcs to ensure things are working properly. You could also try AI and tell it your formula and what you're trying to do to get the result. They're good 'ol chatGPT but also formula bots like Coefficient's GPT Copilot.

Do not use 2 digit years..

You will only get out of Excel the quality of the data you input.

Do not take shortcuts..