Hi Dear,
I need formula that allow me to see what is due to pay (today) I prefer id ID column will highlight when today's day I have in my original spreadsheet formulas with today's date. I think the new formula should contained cell like "today's day" + ID Column and some of the cell with payment (see yellow)
so will the payment due , be in the column for the month ?
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi,
No, the payment will be only when yellow cell appears, see for example July 2013 has payment (O3 and in O4 has amount) then I would like to know that is due to be pay in the same day, for example if today is the 24.04.2014 the formula needs to find the payment which is due to pay today by highlight ID column.
Yes the payment will appears in columns from G4 forward.
I have used
=AND( TODAY() <= EOMONTH(G$2,0),TODAY()>=G$2,G3>0)
as a conditional format and that will highlight any cell that has a value in and the Month in the title is the same as today time
Thank you Etaf so much, it helps me any way but could we change the formula to highlight me ID column only instead month?
I'm sure theres a way to do this with arrays or lookups - But for the moment , I have had to setup some helper rows - they do not have to be in this location and they can also be hidden
see attached
Ok Thank you so much, i will try do it on my own spreadsheet and will see how it works
OK so i have changed the layout so that the test is on Sheet2 and also changed the conditional format in A on sheet1
that may work better for you on a different sheet
Thanks, for that,
see when I insert the formula (false) to my spreadsheet i think that it no working properly or I am doing something wrong, in april I should get "True" because payment is due that month but it show me "false" for some reason. I checked formula twice and I cannot find any
problem
can you post the spreadsheet ?
is it confidential information - you could email me if allowed
i can send my email via a PM here
we do not put emails into public forums
please see file with print screen
Last edited by jowii307; 04-26-2014 at 04:20 PM.
see the thing is I have spreadsheet from my work and I cannot pass it to the public, however when I paste one cell into new spreadsheet everything going crazy, so thy why attached spreadsheet with print screent
i understand
can you post a screen shot then ?
have sent you over see post above
opps missed that
you need to change the G2 to the new date cell
AC9
I assume the data entry starts at AC10
so
=AND( TODAY() <= EOMONTH(AC$10,0),TODAY()>=AC$10,AC3>0)
The thing is that I changed cells, data entry starts at AC9
I noticed in your formula space between first bracket and today and next space after third bracket and before sign it is crucial?
no
my bad
=AND(TODAY()<= EOMONTH(AC$9,0),TODAY()>=AC$9,AC10>0)
The date header is in row 9 and the amounts start in row 10
correct ?
sorry entry starts from AC19 however AC9- date (month and year), my formula looks for first entry like that =AND( TODAY() <=EOMONTH(AC$19,0),TODAY()>=AC$19,AC9>0)
=AND( TODAY() <=EOMONTH(AC$19,0),TODAY()>=AC$19,AC9>0)
if the date is in AC9 and the data in AC19
it should be
=AND(TODAY()<=EOMONTH(AC$9,0),TODAY()>=AC$9,AC19>0)
the reason of big gap between cell is earlier sorting in me sheet
ok - will this range change if sorted then ?
for some reason no working in my sheet, i know in you previous working correctly but for my no
let my doing something
in row 9 you have the dates formated as before
1/month/year
and in row 19 you have the data entries
assuming the column that has 1/Apr/2014 in column AT
so AT9 is 1/4/14 as a date and NOTE text
so the formula
TODAY()<=EOMONTH(AT$9,0),TODAY()>=AT$9
is looking to see if the eomonth for the date in AT9 so if you put
=EOMONTH(AT$9,0)
into a cell
do you get 30/apr/14
then the TODAY()<=EOMONTH(AT$9,0),TODAY()>=AT$9
is checking that
today() 26/apr/14 is less than 30/4/14
which is is so true
then
TODAY()>=AT$9
is checking if today() 26/4/14 is greater than 1/4/14 - which it is
so in a cell put
=AND(TODAY()<=EOMONTH(AT$9,0),TODAY()>=AT$9)
you should get TRUE
the next bit
AT19>0
is testing to see if there is any data entry in that cell
if there is you get TRUE if not you get FALSE
No understand what is happening I just copied a few cells from my work spreadsheet to new sheet and there everything works fine it shows me true but in my work sheet no?
have you tried each part of the formula as shown in my last post
as a Test in a blank cell put
=AT9 + 10
that should now show , as in Glasgow - UK
11/4/14
if AT9 is a correct date
if it does then in another blank cell put
=AND(TODAY()<=EOMONTH(AT$9,0),TODAY()>=AT$9)
is that TRUE?
working now with your last formula =AND(TODAY()<=EOMONTH(AT$9,0),TODAY()>=AT$9, THANK YOU SO MUCH. I do appreciate you time spent on mu issue
cool
but that needs to check if any data - payment is entered otherwise it will just be true whenever the date is correct for the column
I will check that each month is that working, any way THANK YOU so much!!
yes i ll be checking each months if working properly. Thanks once again!!
Hi just wonder if you could help me with something else?
try
=IF(ISBLANK(E5),IF(ISBLANK(F5),IF(ISBLANK(G5), IF(ISBLANK(H5),IF(ISBLANK(I5), "all Blank","Bad Debt"),"90 Days"),"60 Days"),"30 days"),"current")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks