Determine Weekday Of Date

Sep 19, 2007

The code is ok until I hit a month with 4weeks in it and days left over.
The code puts a week total in place of a Sunday, but as some months end before a Sunday appears the code just builds a Month end total sheet.

What I need in this case is a Week total even if there is no Sunday before it builds the Month end Total.

I hope i'm being clear

Sub NewSheets()
Dim Dte As Date, Dy As Date
Dim i As Long, j As Long, Dys As Long
Dim CountWeek As Boolean
Dim Shts As Long...

View 3 Replies


ADVERTISEMENT

Determine Weekday Entered In InputBox

Aug 12, 2006

How do i change this code so that before going on to the next box, the entry must be: Monday, Tuesday...Sunday.

spellday = vbNullString

Do While Len(spellday) < 2
spellday = InputBox("Day of week")
If spellday = vbNullString Then
spellday = MsgBox("Press OK to Cancel Entry" & Chr(13) & "Press Cancel to return to program", vbOKCancel)
If spellday = vbOK Then
MsgBox "Program Closing" & Chr(13) & "Reopen file to run program."
ActiveWorkbook.Close
End If
End If
Loop

View 3 Replies View Related

Weekday/workday Date 1 Month After The Last Contact Date

Sep 20, 2009

I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.

I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.

So, if the date is on a weekend, I need the Monday date instead.

View 5 Replies View Related

Number Of Days To Add To Date Based On Weekday Of Date

Apr 23, 2008

I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008

View 4 Replies View Related

If The Date Is A Weekday Then...

Feb 19, 2010

I'm trying to do an IF function involving the date. Basically if the current day is a weekday then I want the cell value to be 30. If the current date is a weekend then I want the cell value to be 50. I'm pretty lost on how to write the formula.

View 3 Replies View Related

Weekday - Calculate Date

Jan 29, 2008

Im trying to calculate a date in excel.

A2 = 28.01.2008
A1 should provide the date from A2 minus 1 weekday. (so 25.01.2008)

View 9 Replies View Related

Show Weekday Of Date

Jul 14, 2007

I am looking for excel to return a day from a date value, 14/07/07 = Saturday. Need this in VB ?

I have tried the DATE() and TODAY() etc.. Do i need to first tell excel a day by date so it can work it out, or can i do it in code???

The reason is im looking for it in VB to generate a report on a weeks data, which is inputted by a user. I will know the first date will be a range 1, but then need to convert it into a Day name ?

View 5 Replies View Related

Date Of Last Weekday In Month

Jun 5, 2008

I used a formula I found on this site to find the last friday in a month. = DATE(" & Str(iYear) & ",1+1,0)+MOD(-WEEKDAY(DATE(" & Str(iYear) & ",1+1,0),2)-2,-7)

Is there an equivelant date function that can be used when coding in vb (not within a cell.) The VBA editor does not recognize this. I believe it is because date is reserved for variables. If anyone can make this line of code work in vb so as I dont have to asign it to a cell in a worksheet you would be my hero.

View 4 Replies View Related

Display Weekday When Date Is MM-DD-YYYY

Oct 22, 2007

I'm looking to display the weekday in a cell, based on the date in cell A1. I know the code I can use is

View 5 Replies View Related

Count Of Text Associated With Date By Weekday

Jun 2, 2008

The D column is a date (formatted ddd d/m/yy) and the F column contains a string variable such as Red, Blue, Black (D2:D310 & H2:H310). I need a formula that can count how many times the word "Red" occurs on Monday, Tuesday, Wednesday, Thursday, Friday. I am showing the results in a table in which Q4:Q8 are the weekdays and R3:W3 are the names of the colours to be counted.

View 4 Replies View Related

Computing The Date Of The Next Occurring Specific Weekday?

Feb 18, 2014

My spreadsheet opens. Cell A1 determines what today is, formatted as "weekday, day month year" (e.g., "Tuesday, 18 February 2014").

I have another cell in which I typically enter the date for the coming Friday. Instead of updating this field once each week, I'd like to calculate the date for the next coming Friday based off the value in cell A1 (to be concise, if "today" happens to be a Friday, then it would calculate the date of next Friday). (Continuing the example above, the coming Friday would be computed as 2/21/2014. If I open the spreadsheet on 2/21, it would calculate 2/28.)

What formula or step(s) to follow to render this value automatically?

View 4 Replies View Related

Find Date Of Weekday In Previous Week.

Sep 27, 2009

I've been racking my head on this one.

I need a function where for a given date will return the date of a specified weekday in the previous week.

Example: If today is Friday Sept. 25th 2009 and I want to find the date of
the Wednesday in the previous week I would need something
like DATEPREVIOUSWEEK("09/25/2009", vbWednesday)

View 9 Replies View Related

Add Days To Date According To Weekday Of Date

Aug 15, 2007

Based off of the current thread: [URL]....

However I need to add certain # of days depending on what day of the week it is. Here is what I have so far.

=IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=2,M7+2, M7+1))

M7 is a date entered by the user, This formula is in N7.

If the date is a Monday or a Wednesday, the formula works...

If its a Tuesday, it works (M7+1).

However if the date is a:
Thursday
Friday
Saturday
Sunday

I need the formula to determine that and add the appropriate # to bring it back to that monday.

For example, person enters 16 Aug 07 (Thursday) it needs to add 4 days to bring it back to display 20 Aug 07.

I think I would run out of IF statements for this formula.

View 9 Replies View Related

Add Days To Date According To Weekday Of Date

Aug 15, 2007

Based off of the current thread:
Display Time Depending On Weekday

I borrowed some of the formula to help rectify my problem, however I need to add certain # of days depending on what day of the week it is. Here is what I have so far.

=IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=2,M7+2, M7+1))

M7 is a date entered by the user, This formula is in N7.

If the date is a Monday or a Wednesday, the formula works...

If its a Tuesday, it works (M7+1).

However if the date is a:
Thursday
Friday
Saturday
Sunday

I need the formula to determine that and add the appropriate # to bring it back to that monday.

For example, person enters 16 Aug 07 (Thursday) it needs to add 4 days to bring it back to display 20 Aug 07.

View 9 Replies View Related

IF And AND; Add A Line To The Code So Date Picked By The User Needs To Be Both Weekday And Not A Day In The Past

Jun 20, 2006

See attcahed code. I need to add a line to the code so date picked by the user needs to be both weekday and not a day in the past.

e.i today : 20/06/2006

if user pick a date before today or a weekend date pivot table does not update.

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Application. ScreenUpdating = False
If Weekday(MonthView1.Value, vbMonday) < 6 Then
With PivotTables("PivotTable1")
.PivotFields("DATE").CurrentPage = Format(MonthView1.Value, "dd/mm/yyyy")
End With
End If
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Determine Last Modified Date

Oct 1, 2008

I have a pivot table for which I load data from a .csv file. Is it possible to include a timestamp on when the .csv file was last loaded. If I do it above the actual pivot table, it just shows when the pivot table was refreshed, but not when the .csv file was last loaded.

View 4 Replies View Related

Determine Month Of Date

Oct 31, 2006

how to put this in

If the date in C3 = January 31 then do something.

I don't know how to put the date part in code.

View 5 Replies View Related

Determine Day Name Of Current Date

Oct 23, 2007

I have got a spreadsheet that needs filling in by 3pm everyday. The workbook has a worksheet for each day e.g. Mon to Fri. What I am looking to do is fill the worksheets in for whatever day it is e.g. if it is monday then select monday data from required file and paste into monday worksheet. I was going to create five macros for each day and then select the required one on the day, but rather than doing this is their a way that i could write one macro that runs by checking what day it is? e.g. if monday run monday code...

View 5 Replies View Related

Determine If Date Is Between Two Dates

Apr 1, 2008

I have a formula that tries to determine if a date is between two dates. The formula I have is =IF(AND(A1>=A2,A1<=A3),"yes","no")

a1 = 7/31/2009
a2 = 8/1/2008
a3 = 3/31/2010

If the date is between those two dates then "yes", if not then "no". I tried this formula a couple times and it works when a3 is 3/31/2010 but when I try 3/31/2009 for example it does not work. So it seems like there is something wrong with the second date I am referencing.

View 3 Replies View Related

Using Reverse Date Of Birth To Determine Age

Dec 8, 2008

I'm trying to use a list of date of births to determine an age output.

However the date of births are all in a simple reverse formate, eg

19790822 would be 22nd August 1979

Ideally, i would take this number and the output would just be the persons age in whole years.

View 3 Replies View Related

Determine Accounting 4-4-5 Month From Date

May 7, 2009

I am trying to determine if a specific date is what week in a 4-4-5 accounting month. See attached file. Is there a formula that can be used to do this???

View 4 Replies View Related

Determine Current Date In Macro

Nov 7, 2006

I'm trying to write a batch file that connects to a database and passes in an SQL query. One of the conditions of this query is the date. I need the batch file to figure out what todays date is and pass that in.

This is what I currently have.

ws_tran_date between to_date( '&datefrom 08:30:00AM', 'DD-MON-YYYY HH:MI:SS AM') and TO_DATE('&dateto 08:30:00am', 'DD-MON-YYYY HH:MI:SS AM')

When I run the batch file, it asks me to input the dates manaully in the format DD-MON-YYYY (eg 7-NOV-2006). I need the batch file to figure this out on it's own.

Is there some function I can call to do this?

View 5 Replies View Related

Determine Days Based On Due Date

Sep 1, 2007

I am attempting to create a template to input specific dates. If a student submits an assignment on the due date, I have set up the spreadsheet to conditionally format whether the assignment was late or not. In addition, I have a column for when feedback is to be submitted and whether it was on time or not. My problem is this...

I cannot figure out a formula or a way to determine:
If the student submits the assignment late, when will the feedback be due? For example, if submitted one day late, the instructor has an extra day to submit feedback.Two days late is an extra two days to work on it etc..

View 4 Replies View Related

Determine Last Saved Date Of Workbook

Sep 7, 2007

I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?

Private Sub Worksheet_Activate()
If application.worksheet.lastsaved = msolastweek Then
range("RANGE").clearcontents
End If
End Sub

View 7 Replies View Related

Determine Date Of Cell In For Each Loop

Mar 11, 2008

I have a for/next loop working through a date range. I want to skip a date at the 'next' code line.

1st loop Date = 1/1/2008
miss 2/1/2008
2nd loop Date = 3/1/2008

For Each Date In Rng
Sheet1.copy
Sheet2.copy
Next Date+2

View 9 Replies View Related

Determine Date Of Nth Specific Day Of Month

Apr 9, 2008

See attached spreadsheet. I have a set of Jobs with a set of end dates. A meeting date should be held after the end of each job. I need a formula or macro to determine when the meeting date will be held? The criteria to determine the meeting date is found in rows 12-18. The end dates are set on different days of the weeks and different weeks of the month depending on the region that the job is located in. The meeting dates will be input in colums d, f, h & j.

View 7 Replies View Related

Determine Date Return A Value From Adjacent Cell

May 2, 2014

column 1 is vouchers
column 2 is date

vouchers column is populated with a voucher code for every date date column is populated with dates beginning with today, ending with the last day of the year.

I would like to create a function that evaluates the date file, If date = today's date, return value in the adjacent vouchers column.

View 1 Replies View Related

How To Determine Oldest Date In Range Of Cells

Jan 30, 2012

I am looking for a formula to determine the oldest date in a range of cells. BUT, the part that's kicking me in the pants here, is that the range will change.

In column D, I have a person's name. The number of times that person's name will appear will change every time I run this formula. In column F, I have a date & time. I'm looking for a formula that will determine the oldest date/time associated with that person.

View 2 Replies View Related

Macro IF To Determine If Cell Has Today Date

Mar 28, 2012

I've been using the following code and would like to add another IF that checks the value of a cell to see if it holds today's date.

Code:
If Range("E" & CStr(LSearchRow)).Value "Closed" Then
If Range("E" & CStr(LSearchRow)).Value "Closed w/o Customer Confirm" Then
'Select SR in Sheet to update
Range("B" & CStr(LSearchRow)).Select

[Code] ......

I tried to modify it to...

Code:
If Range("E" & CStr(LSearchRow)).Value "Closed" Then
If Range("E" & CStr(LSearchRow)).Value "Closed w/o Customer Confirm" Then
If Range("AK" & CStr(LSearchRow)).Value "=today()" Then
'Select SR in Sheet to update

[Code] ......

I've tried a couple of other things but no success

View 5 Replies View Related

VLookup Today Date To Determine File Name

Aug 21, 2013

Im working on a document for work, its sort of a learning hobby tbh, something i want to learn. Im trying to do things myself from the books etc, vids from the net. This one i cant find the answer to so before i go ahead i would like to see if it can be done

Basically, we have a few documents that change every day. we get the raw data, and delete coloumns, change the size to autofit width etc...

(im creating a main macro to do that now)

But with that data i need to..

open a new book (but the book name is never the same... book1, book2 as you know)
paste the data
save it

.... the file name! needs to be something like this "Department 20130401" which i saved on 01/04/2013

if i did the document on 10th september.. it would be "Department 20131010"

I have Coloumn A with the dates 01/01/2013 etc
and Colounm B with Department 20130101 etc
Cell ("I1") has =Today() in it

could a macro change the filename from a vlookup result?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved