# Formula: Current Month Based On System Clock

Nov 30, 2009Is There a formula for current Month based on system clock....

View 10 RepliesIs There a formula for current Month based on system clock....

View 10 RepliesADVERTISEMENT

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...

How to modify the code a bit...

VB:

Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown

I tried to change the code, but it did not worked.

How do I run this loop 10 times ....Then using a timer or system clock to restart it.

Code:

Public Sub Workbook_Open()

Dim ws As Worksheet

Do

For Each ws In ThisWorkbook.Worksheets

[Code]....

I have log data in two columns:

Column A: Date/time (at 30 minute intervals)

Column B: Numeric data

On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.

Manual

=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

I have a tab set up to display information that has been colletect on other tabs. What I would like to do is change this information depending on what month is selected.

For example, if I select December then data would be pulled from other tabs and displayed. If I Select June the information would change and select other data from the same tabs.

create a Macro that runs when system clock changes minute and then at every change of minute of system clock.

For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...

I already have a Macro that runs every minute from initial run time, using

Code:

Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"

but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.

I have a spread sheet with towns listed in Column G from row 3-81. I months going across row 2 as column labels (they are dates in Jan-14 format). They go from column H-AF.

What I want to happen is to have a formula (not in VBA) which will compare the month in row 2 versus the current month. If it is more than 5 months ago I would like it to return the town in column G for the corresponding row. The only thing I am using to "mark" the last time the town was worked is an X so that is all it needs to look for.

I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions. I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June).

I have used Month(Now()) to generate the current month value in cell B1. I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.

finding a code that would automatically insert various phrases into a column depending on whether or not the person in that row was due to renew their membership in our organization. The code I received works fantastically, but I've come across one snafu:

View 3 Replies View Relatedget the current month name in a Cell A1 and Next month name in Cell B1. what formula should I used? This will be a part of the macro that I'm creating.

The macro will be use every 11th to the last day of the month so I want the formula to be dynamic enough to work in each day the macro will be use.

Expected result using the current Date Today

A1 B1

February March

I have sheet1 where there is a data.

Column A has dates and column F, G, H have some text with ',' ( e.g. Paper, wall, taps...etc)

I want to now combine text values from Column F ( rows of current month only) and place them in sheet2 in one cell with ',' in between text and not showing same item twice.

Sample book attached ....

I use this formula to count number of entries month wise.

Following example is for Month of Feb.

How can I convert this to count entries for current year ( of course month wise).

I have the following data:

column a: column B:

1

7

9

25

I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:

column a: column B:

1 09/1/2009

7 09/7/2009

9 09/9/2009

25 09/25/2009

I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.

Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?

the MONTH(NOW()) Function, as I need it to reference the month preceding the current month.

View 3 Replies View RelatedI am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.

The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.

Attached is an example : Budget Example.xlsxâ€Ž

I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.

View 5 Replies View RelatedI m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:

Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)

- $25K-100K, recognize in two equal parts in months N + 1 and N + 2

- over $100K, recognize in three equal parts over 3 months

N + 1, N + 2, N + 3 ...

I have series of data values like below. I have to find Maximum, Minimum values for each of these values.

9430

9822

10070

[Code].....

I'm working on a spreadsheet which needs to refer to the current date. My PC checks the system date/time daily. Can I assume that if an end-user regularly connects to the internet, their PC will have a correct system date? Does anyone know if Win2000 or Win98 automatically update the system date/time when connected to the internet?

View 4 Replies View RelatedI am trying to figure out the written formula for needing this answer:

For a time clock with 6 minute rounding.

If I type in 8:20 as the arrival time, I need it to round to 8:18, so that the final time will be in tenths.

Here is the rounding:

Actual Rounded Minutes in Time Clock

:57, :58, :59, :00, :01, :02

:03, :04, :05, :06, :07, :08

:09, :10, :11, :12, :13, :14

:15, :16, :17, :18, :19, :20

:21, :22, :23, :24, :25, :26

:27, :28, :29, :30, :31, :32

:33, :34, :35, :36, :37, :38

:39, :40, :41, :42, :43, :44

:45, :46, :47, :48, :49, :50

:51, :52, :53, :54, :55, :56

Anything in the same line will round to the bolded time.

Example: I arrive to work at 8:09 and go to lunch at 11:59, it will round to 8:12 and 12:00, making my time worked 3:48 or 3.80. Then I get back from lunch at 12:59 and leave at 17:14, which will round to 13:00 and 17:12, making my time worked 4:12 or 4.2 for the 2nd 1/2 of the day and 8:00 or 8.0 hours for the day.

Time In----------Time Out----------Total Time----------Decimal Time----------Total Hours

...8:12.................12:00..................3:48......................3.80

..13:00................17:12..................4:12......................4.20..........................8.00

The problem is I want to write the actual time and not have to round everything myself and yet I need the decimal time to match what my time clock actually calculates from.

My current formula for the decimal shown above as 3.80 is this: =(HOUR(F7)*60+MINUTE(F7))/60

My current formula for the total time for the day (regular hours) is this: =IF((((E7-D7)+(I7-H7))*24)>8,8,((E7-D7)+(I7-H7))*24)

My current formula for overtime is this: =IF(((E7-D7)+(I7-H7))*24>8,((E7-D7)+(I7-H7))*24-8,0)

This is not a time clock, it is my personal copy of what my time for the week was/is, so that I can compare it to the actual time card that I have to sign off on.

I am looking for 3 outputs from the attached report. Output report also attached.

1.Under the column "Created_date"(Column I), I am trying to count the number of rows which has current month in column L and result in output report file under "Risks Added" column.

2.Find and count the number of rows which has been modified this month by using "Modified_Date"(Column N) column and result in output report file under "Risks Updated" column.

3.Compare the column "Status_Change_Date" which is updated this month and "status". If that row has the Status column as 'Resolved' and the 'modified date' column has current month, please give the count of those rows in the output report file under "Risks Mitigated" column.

Below is the basic vba code for copying the rows which has this month created date

[Code] .....

Attached Files:

Sample Report.xlsxâ€Ž

Output Report.xlsxâ€Ž

I don't think there is a built-in function for retrieval of the last day of the month, is there?

Does anyone know how I can retrieve the last day of month using VBA?

So that I can use it like DATE.

I want to compare performance data YoY, updated monthly on a YTD basis.

Sheet1: columns of data as follows

Jan11 Feb11 ... Dec11 Jan12 Feb12 ... Dec12

1000 200 ... 500 800 900 700

...

Sheet2: If we have data through July 2012, I want to show a sum of Jan-Jul11 against Jan-Jul12 on a separate sheet.

Cell1 (updated each month) = July

ColumnA: Jan-(cell1) 2011

ColumnB: Jan-(cell1) 2012

Is there a formula I can use in columns A & B that will reference cell1 and update using the data from sheet1 automatically when cell1 is changed each month?

I am working on spreadsheet that calculates yearly totals. It is set up similar to this:

Jan Feb Mar Apr May ETC. TOTAL

Charge 123 123 123

Goal 223 223 223 223

Cash 111 111 111

% Coll. 50% 50% 50%

The "goal" column is Self calculated for the current month (I.E. it's April, that goal is calculated) The "Charge, Cash, and % Collections" columns won't be fill in for April until its over.

have it add Aprils "Goal" but not add "Mays" and so on... Doing this for the entire year.

I'm using this formula to count how many times the date in column C and the text "WON" appears in column I and it falls within the month & year that is in Z65.

=COUNTIFS(INDEX(C:C,$AL$15):INDEX(C:C,$AL$17),">="&DATE(YEAR(Z65),MONTH(Z65),1),INDEX(C:C,$AL$15):INDEX(C:C,$AL$17),"

I am a memeber of this forum for more than a year, and currently assign into a new assignment which dealing an excel file everyday. We have one excel file for moniroting of action items generated by the management after the study. As since there were around 2,500+ rows has been generated since in the beginning of 1990's till todate. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this month out of the total numbers of action items.

Is it possible to use the COUNTIF formula to count the number of items in Col C, where Col B contains a date?

2-Jan-13 Closed

2-Jan-13 Closed

5-Jan-13 Closed

19-Feb-13 Closed

16-Feb-13 Closed

22-Feb-13 Closed

2-Mar-14 Closed

5-Mar-14 Closed

8-Mar-14 Closed

10-Mar-14 Closed

15-Mar-14 Closed

Please tell me how to find the birthdays in the current month.I m uploading the worksheet for reference.I want to know the formula in this context.

View 9 Replies View RelatedI have a worksheet that displays all the current months weekdays from a starting date in cell b2 using the weekday formula it works accross a row checking next day is a weekday and adding 1 but since some months have more weekdays than others the few cells that are sometimes not needed are then filled with a weekday from the next month how can I stop this? and only have the current months weekdays

View 3 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved