Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

exceldate-time
MS Office Version: Excel 2016
posted by: Newbe4excelmacros on Sun Nov 10, 2019 at 12:20 pm
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: 4889) 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

posted by: k1w1sm (rep: 197) on Sun Nov 10, 2019 at 2:06 pm
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