Macro Required to add static date and time when IF condition is true(PAID)

**Danymanson** · 05-26-2017, 10:45 AM

About the Workbook

This sheet contains three sheets 1) Packing List.
2) CL LEDGER
3) Automated Invoices.

Background : I usually Add Debit payments(Column I) in CL Ledger sheet periodically.Adding a payment connects with accumulation on Automated Invoices sheet (Total Payments for each of the client ) using Sum if formula. As soon as it checks that total invoice balance in M3 COLUMN ( UNDER Auomated Invoices Sheet) goes from negative to positive, it marks the invoices as "PAID".

My Question:
Once it gets to Paid from the existing status " Unpaid " I'd like to see that particular Date and TIme in column 43 under column "DATE" . (changes in between two sheets and not manual). Thinking Macro can automate dates whenever any invoice goes Paid after Debit payment in CL ledger.

Any Macro Formula Help will be much appreciated. Thanks very much Spreadsheet attached.

**leelnich** · 05-26-2017, 10:53 AM

Hi Dany - Can't access your workbook, it's password-protected.

**danymason** · 05-26-2017, 11:50 AM

Sincere apologies leelnich..

Please find attached the spreadsheet without a password. Many thanks again for your kind help...

**jeffreybrown** · 05-26-2017, 12:07 PM

In-between the time before leelnich makes it back to the forum, I have a question.

Really hard to follow your sheet, so can you give a couple of examples?

If the two sheets, CL Ledger and Automated Invoice sheet are not connected, why not?

How do you ensure information necessary between the two remains equal?

When a Debit is added, what is the link from CL Ledger and Automated Invoice sheet?

Anyway, this is your sheet and further specific explanation would help for at least me.

**danymason** · 05-26-2017, 01:11 PM

Thanks Jeffery for taking your time out in reviewing this spreadsheet. Jeff, I am sorry for the complexity since this is more of a evolving requirement columns that emerge. Here are my thoughts:

1.
Examples:
I record an entry under Packing List for fabrics. Example" Leader Industries" --- Total Yards and Normal price.
2. Next, I record this in my Ledger i.e. total yards * price( gives my amount for bill # X). Credit column to keep track of credit sales.
3. I then, manage a sheet "Automated Invoices", this from Cell B47 onwards takes up the CL Ledger Credit amount cell reference in order to keep a balance in evaluating they equate.
4. To keep things under control I generally multiply yards * Price to equate Credit Sales amount under CL Ledger Sheet (Credit column).
5. If you check cell j3(Automated Invoices) you'll figure out CL Ledger is very much connected in terms of summing up Debit payments
for each of the client individually, similarly total bill amount take up all credit sales from CL Ledger sheet.

6. Adding Debit amount adds up to the "Clients Total payments" under Automated Invoice and thus in totality checks whether the negative Invoice Amount under M3 (Automated invoices) turns from negative to positive.
Positive means the Bill is Paid and plus you may check further logic up on Paid, Unpaid column if Condition.Thus for example I add up 500000 under CL Ledger (Debit)for Leader Industries , this in turn increases Total Payments under Automated Invoices , further it adds up
under total Payments and turns down negative invoice balance. Therefore, you'll see "Unpaid" but by after entering Debit amount for a particular client the number of invoices changes to "PAID".

Hope I have clarified some ambiguity. Thanks again for your helping Jeff.

**leelnich** · 05-26-2017, 01:24 PM

Hi Dany- Not sure what's going on with AUTOMATED INVOICE!column AG. ("STATUS")(3rd instance; columns I, AE also "STATUS")
=+IF(AE3="NOTPAID",N3:N283,"PAID")

Theres no array operation being performed on that range, and it just stops at an arbitrary point halfway down the column. Also it's a relative address, so it changed as it was copied down.

But I digress. There are 5 columns showing "PAID"!! [I, N, AA, AE, AG] 3 are labeled "STATUS", 1 is '"STATUS/DURATION", and the 5th is INVOICE BALANCE.
1) Which column or Value should trigger the TimeStamp?
2) How would you like the Date + Time formatted EXACTLY? (ex. 30-Apr-16 14:22)
3) Just to verify, TimeStamp goes in AUTOMATED INVOICE!column AQ, correct?

It should be noted that the timestamp, while static, will NOT be permanent. That requires the document be saved. Do you wish to automate that as well. (I don't recommend it, at least not while the document is under development.)

**danymason** · 05-26-2017, 02:37 PM

Hi Leelinch, Many Many thanks for pointing out these specifics.

My Answers:

1. AE column represents Paid/ Unpaid with future cheques( Advance payment cheques) thus you can see perhaps "Aamir Variety Silk Center"
client shows difference of payments. Column I takes all payments until NOW() while, AE column takes all payments Debits further into future cheques.

My needs are certainly in AQ and AR columns with date and time stamps representative of columns (I) and (AE)Paid/ Unpaid status changes.Rest columns can be ignored/removed as per need.

2. This will be exactly the formatting I am interested in ((ex. 30-Apr-16 14:22)).

3. As advised I'll be fine saving those static entries. I generally after adding Debit Entries to ledger save workbook. If that saving suffice the static timestamps, I can think of being a happy camper

.

Your Help is greatly needed and many many thanks again for your BIG help.... Please let me know if I miss anything and I'll be quick with an explanation to it.

**danymason** · 05-26-2017, 03:03 PM

One more request in addition to above. While I am at it, I would like to see from Bill Date until the Paid Date, how many days have been actually passed. Is there a possibility with your above formatted Date and Time I can calculate the difference. Example c3 bill Date is Nov 19th and Paid date/ time stamp in column AQ shows February 10th 14:22). Can it takes the difference between the Nov 19 and Feb 10th and Perhaps show in column "AS" Number of Days (53 Days---diff of Nov 19-2015 upto Feb 10th 2016) next to column AQ and AR.

This will be very useful in knowing when from the Billing date it converts into Payment. Many Thanks again ...Sorry if I am asking too much.

**leelnich** · 05-27-2017, 08:59 AM

Hi Dany- Sorry this took so long, yours is one of several active projects. I looked at the problem and decided to add a formula to the DATE column (AQ) which reads PAID or UNPAID (= column I). Then I wrote a subroutine AddTimeStamp that searches that column, changes cells reading PAID to the current Date, and fills in the Time and Days Elapsed columns. AddTimeStamp is triggered by the Worksheet_Calculation event, which "fires" whenever any input to any formula on the 'AUTOMATED INVOICE' worksheet is changed.
The Workbook_BeforeSave event also triggers AddTimeStamp, first adding the PAID/UNPAID formula to any blank cells in AQ to keep it synchronized with column I.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

**Danymanson** · 05-27-2017, 09:06 AM

Thank you so much Leelnich for your Big Help. I am sticking around to check the file and follow exactly your directions. Thanks very much for taking your time out and devising a mission critical formula.

**Danymanson** · 05-27-2017, 09:30 AM

Lee, With your kind expertise and knowledge I'd like a little bit of further clarification as you've embedded lots of technical stuff in it.

My Understanding:
1. You've added AQ column that marks all PAID invoices with current date. and rest with NOT PAID status (referencing Column I cells).
2. As soon as the Paid calculation triggers from CL ledger Debit entries this will in turn show date, a time stamp.column AQ and AR (correct)?
3. Days Elapsed is just Fantastic addition to it.
4. My thought is would this date be changed with the change of Computer clock. As in, tomorrow when its gonna be 28th May, would this be all 28th May or would it be static and not changing with computer clock?

Rest, Lee you are AWESOME person and Big Thanks for this fantastic Help!!!!

**leelnich** · 05-27-2017, 09:50 AM

The times and dates are static, they will not change. Question: do you intend to change the layout of your AUTOMATED INVOICE worksheet? Perhaps get rid of 1 or more of those 'Status' columns, etc? I ask because the code as written would require modification.

**danymason** · 05-28-2017, 07:05 AM

Hi Lee,

Sorry for my delayed response unfortunately some emergency. Thanks a million for making them static. Well, I really have no intentions now for any modifications or trimming down columns. I feel there is a great utility in using this formula especially time elapsed. Let me know if you still suggest I should be trimming down columns and have this re coded?. Also, similar to "I" column you did, could "AE" column that takes care of status PAID/UNPAID for Future payments. Can that be done similarly with further extension of three more columns. This will be helpful in determining how much time one is taking under paying future cheques in payment( Date/ Timestamp and column for time elapsed). Is it easily doable as the logic is all same as of column I. Thanks very very much... You are awesome!... thanks again .

**leelnich** · 05-28-2017, 10:21 AM

Yes, I could repeat for column AE. Questions:
1) Are the time columns actually necessary. I would think not.

**danymason** · 05-28-2017, 11:38 AM

Thanks so much... I am attaching the final spreadsheet which I have basically embedded in all three areas with the code: 1. Workbook; 2, Module, Automated Invoices code and get this working. However I am facing a couple of errors at times. I am attaching this spreadsheet and will be superhelpful if you could get AE column working too.

1) Yes time column is not that necessary I think the same... Again many thanks and looking for the best spreadsheet with I and AE date/ time elapsed columns.

About Errors:
I entered one of the Client entry in CL Ledger Debit entry with 100000 and name of client. It goes run mismatch type error and thus I clicked on Debug and the second screenshot attached that shows highlighted line.

**leelnich** · 05-28-2017, 07:28 PM

Hi Dani - On the AUTOMATED INVOICE worksheet, several columns stop short of the bottom row. Among these are columns AB:AG, which range includes AE, a column you wish to track. Have you ceased updating these columns, or do you intend to extend the formulae to the bottom row so that AE is up-to-date?

**danymason** · 05-29-2017, 05:00 AM

Hi Lee you are right not extended as yet. I'd like to drag Ab,Ac,ad and Ae columns in particular to the bottom. Rest is some clutter in between that i am ignoring for now . Thoughts? Thanks v much again

**Danymanson** · 05-29-2017, 06:24 AM

Attached sheet that contains extended range for columns AB,AC,AD and AE. (PAID/UNPAID WITH FUTURE PAYMENTS).

Please find attached. Thanks again for your help.

**leelnich** · 05-29-2017, 10:38 AM

OK, I see you've used the Name Manager before, so I've modified the code to use a Named Range VBAdate to determine which columns to date-stamp. Currently, it's defined as ='AUTOMATED INVOICE'!$AQ1,'AUTOMATED INVOICE'!$AS1, but can be changed to suit your needs. This approach is more flexible, as Named Ranges adjust automatically to any changes in layout. The code searches the entire columns of any cells listed in VBAdate for the value "PAID". Each cell where it's found is changed to the current date (static). The code no longer processes time or elapsed days-you can use formulas for that. Each column named in VBAdate must initially contain a formula that shows "PAID" based on whatever conditions you define. Other values are ignored.
NOTE: As with ALL columns, you must extend this formula downward whenever you add new rows. This process could be automated, but that should honestly be the subject of a separate thread.

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

**danymason** · 05-29-2017, 04:37 PM

Thank you Lee... Anyone reading this post should know Lee is a total Expert here

. I am fully satisfied that I have chosen this platform and luckier Lee is the Expert ready to extend a helping hand...

**leelnich** · 05-29-2017, 04:46 PM

I am most grateful for the praise (and the rep). Happy to help-Lee

**Danymanson** · 06-01-2017, 08:52 AM

Thank Lee, you deserve much much more

. One tiny question Lee sorry for bumping this solved thread, just wanted to ask if the code is backward compatible. What if I remove the entry from CL ledger would it not clear the static date/ time stamp? or would it do. Your thoughts. Many thanks once again. Best- Dany

Macro Required to add static date and time when IF condition is true(PAID)

LinkBack

Thread Tools

Rate This Thread

Display

Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Re: Macro Required to add static date and time when IF condition is true(PAID)

Thread Information

Users Browsing this Thread

Similar Threads

How to get a Static Date for Modified "PAID" STATUS CHANGE

[SOLVED] subtract days between due date and todays date, if condition 1 and condition 2 are true

Static date with if condition

Formula for Calculating Paid Time Off for fiscal anniversary date real time

[SOLVED] Display and Combine Column Headers each time a condition is true in each row.

[SOLVED] How to get date entered recording first time condition is true?

Static Date and Time Macro

Bookmarks

Bookmarks

Posting Permissions