Date Formatting (VBA Coding)

0

In my datasheet I want to calculate the number of "days outstaning" ("G")

I used the networkdays formula[=NETWORKDAYS.INTL(D6;H6)]

How do I stop the counter if the "date collected" is entered in ("F")

I also want to automatically transfer that spesific row when the "date collected" ("F") is entered to "sheet 2". The "current date" coulomb("H") is then not needed.

For the transfer I think that I must create a VBA code and assign it to a "command button"

The aim is to have a working sheet "Sheet 1" and when a "collection date" is entered that row must be transfered to a different "Sheet 2" for data perposus. The counter in ("G") must also be stopped so to see the amount of days it took for collection.

Answer
Discuss

Discussion

Limit yourself to one question per thread. As you see, experts here will be glad to give you one answer. And then the thread will be dead.
Variatus (rep: 3243) Nov 13, '19 at 5:24 am
Thank you
Newbe4excelmacros Nov 13, '19 at 1:55 pm
Add to Discussion

Answers

0

Hi there.

You could make a start by replacing your days in office formula with this

=IF(F2<>"",NETWORKDAYS.INTL(D2,F2),NETWORKDAYS.INTL(D2,H2))

What this is doing is testing if there is a collection date and if there is not then use today's date for the count else use the collection date.

will look at part 2 when time permits - create a copy on another sheet. or someone else might do that for you.

You have a number of options to fire a macro.

A button - as you have indicated.

An action on entering the collection date

or my favourite on activation (selection) of sheet 2.

-----------------

Here you go. 

I have attached a workbook with a macro in the standard modules sheet and a couple of actions in the worksheet's code sheets. 

This shows 2 ways you can invoke the module

A Button or

Selecting the sheet (my prefered) 

It also demonstrates calling the macro. Also, my preference to having the code in the worksheet code sheet. All my code in one place

Hope this helps - sorry I took so long

K1w1

Discuss

Discussion

I have added an update
k1w1sm (rep: 197) Nov 19, '19 at 5:51 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login