Calculation Error Using WEEKDAY()

Sep 11, 2007

I want to calculate the number of weerks in a year. I use the following formula that seems to work

View 14 Replies


ADVERTISEMENT

Calculation Accuracy Error

May 3, 2007

I have an issue with a very simple feature of excel.

When I enter a simple formula like: =1.2222-1.2211 , the answer is not 0.0011 but 0.0011000000000001 (if you increase decimals).

This error annoys me because if I use conditionnal formating (for example change color to red if value is 0.0011) then it will not work !!!

What is the reason of this error? Do you know where this comes from and how to avoid it? My figures usually go after 5 decimals.

If I use "Precision as displayed", the result of the calculation will be the same as before, but the Conditional formatting will work until I display too much decimal figures.

I think it is an issue that is well known and might be already raised somewhere in the forum but I do not know exactly how to search for it (I do not know the exact terms in English...).

I am using Excel 97.

View 9 Replies View Related

Preventing Calculation Error Showing

Jan 30, 2007

Is there any way that the #N/A can be turned off or hidden and the cell left blank?

View 6 Replies View Related

Calculation Error After Passing Values To Variable

Jan 30, 2008

I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.

If ComboBox2.Text = "Pounds (£)" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If

Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text

Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer

View 3 Replies View Related

Method Calculation Error "1004"

Jun 16, 2007

i use this line inside an addin. Application.Calculation = xlCalculationManual. only when I open the XL App, the addin is uploding, so when excution comes to the line, it gives this error: Run Time Error 1004 "Method calculation of object Application Failed". however, when the XL App is already openned, then uploading or unloading the Addin cause no problem

View 2 Replies View Related

Calculate A Certain Weekday

Oct 28, 2009

I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE.

Store 1 - Today's Date-Wednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2 - Today's Date-Wednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3 - Today's Date-Wednesday 10/28/09 = Next delivery date is Thursday 10/29/09.

View 3 Replies View Related

Rounding To A Particular Weekday

Nov 11, 2009

looking for for some help on a fairly simple problem: i've attached a worksheet, and in column B (Due to Supply Chain) i'd like to insert a formula that will subtract 21 days from the date in column Z (Pub Date), and then round that date to the nearest wednesday. is this possible?

fyi: the dates in column Z are in a yyyy-mm-dd format; they don't have to remain that way.

View 6 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

Autofind First Weekday

Feb 22, 2010

Im working on a spreadsheet which has a column of dates formatted in "dd/mm/yy" there are always 365 days listed but these could be between any period. I am trying to create a table which looks up all mondays, tuesdays etc ....

I need a formula to find the first monday in the list and then to fill the rest of the tables. So far I manually input the monday and the rows below use =previous row + 7 to fill the following mondays, then in the next section it will refer to the monday and +1 to populate tuesday then the +7 in the rows below.

Ideally i would like a system which would log these dates automatically, the trouble is, sometimes the Thursday or another day will be first ( eg if the raw data is between 01/01/09 - 30/12/09 then the 01/01/09 is a thursday so the first thursday is 01 but the first monday will be 04/01/09.

View 5 Replies View Related

VBA Equivalent Of Weekday()

Dec 19, 2008

I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.

View 8 Replies View Related

WEEKDAY Function

Apr 21, 2006

I have a running schedule with mutlile tasks scheduled for different day # or the same and would like to have the dates auto fill in once I enter the start
date.

example:
4/20/06 task 1 day 1
? task 2 day 2
? task 3 day 2
? task 4 day 3

Keeping in mind that I don't want to include weekends and that I'll need to delete a range of holiday dates.

View 10 Replies View Related

Weekday Function +1 Day

Sep 25, 2009

this formula in a cell. But I noticed it doesn't work if the day is friday because the next working day would be Monday.

I tried to replace the Today with weekday but I'm not doing something right.

View 2 Replies View Related

Serial No With Weekday

Sep 20, 2007

I am receiving the data from the client , I want the serial no with the day of the date ... like this .. i ope u got it..

Like
thu01 20-Sep 2007
thu02 20-Sep 2007
thu03 20-Sep 2007
thu04 20-Sep 2007
Fri01 21 Sep 2007
Fri02 21 Sep 2007
Fri03 21 Sep 2007
Fri04 21 Sep 2007

Mon01 23 sep2007
mon02 23 sep 2007

I may receive more that 100 mails in a day .. so the serial should get updated of its own.

View 9 Replies View Related

Subtracting 1 Day When The Weekday Is A Thursday

May 28, 2009

Subtracting 1 day when the weekday is a Thursday. I need with the formula above (on AA2):

View 2 Replies View Related

Insert 4 Rows For Each Weekday

Apr 8, 2008

I have a worksheet that has in column A.

Mon
Tues
Wed
Thurs
Fri

I want to insert four new rows under each weekday.
Example:

Mon
blank row
blank row
blank row
blank row
Tues
blank row
blank row
blank row
blank row

I wish I had thought of this before I created 6000 rows consisting
of:
Mon
Tues
Wed
Thurs
Fri

Repeating over and over.

This was setup to track items ordered per day but I forgot I might have to order 4 items each day in some cases.

View 14 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

Average By Weekday Of Month

Feb 9, 2009

I have a sheet that contains a number of documents entered into a system by a user.
Each month those number of documents are average by the number of working days in a month.

I'm using this formula, =AVERAGE(C4,22)

I would have to edit this for each month with the number of working days.

Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?

View 9 Replies View Related

Weekday Summary Table

Jan 14, 2010

I have a list of dates in Column A, with a list of associated values in Column B. I'd like to create a small summary table that will give a count of the dates in Column A and a sum of the values in Column B, broken into a bucket for each day of the week.

I know how to create this table using a short macro that would loop through the list of dates, but if possible I'd like to calculate the values using worksheet formulas so that
I don't have to run the macro each time another set of data is added to the list.

View 9 Replies View Related

Convert If & Weekday Formula To VBA

May 12, 2007

Formula in Range("E4")
=IF(WEEKDAY(E2)=1,"CN","T" & WEEKDAY(E2))

what this in VBA?

View 8 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

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 View Related

Copy Every X Minutes Of Each Weekday

Apr 1, 2008

I have a cell that is set up as an external data source that is constantly changing 24 hours per day 5 days per week. What I am trying to do is copy that cell to another worksheet and save that dymanic data as a static value and save that data every 30 minutes synced to my PC clock.

View 9 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

Populate Data Based On Weekday

Feb 25, 2014

I have worked enough with vlookup and addif functions,

Now I have a different problem.

Please find attached file.

Sheet7 contains transactions numbers based on weekdays.

There are other 6 sheets representing weekdays 1 to 6.

What is the easiest way to populate the relevant transactions in relevant sheet?

For example All transaction numbers from sheet7 that relate to weekday 4, must be copied in column B of sheet "4".

View 6 Replies View Related

Select Problem With Weekday Function

Jun 17, 2009

I'm having problems on something that should be easy. On the basis of a date, just move to a pre-defined day-of-the-week cell. If the day is a Monday, the active cell will end up being the range set aside for Mondays.

Sub findFirstDay()
Dim workDOW As Integer
workDOW = Weekday(Range("b1"))
Select Case workDOW
Case workDOW = 1: Application.Goto reference:="sunFirstday"
Case workDOW = 2: Application.Goto reference:="monFirstDay"
Case workDOW = 3: Application.Goto reference:="tueFirstday"
Case workDOW = 4: Application.Goto reference:="wedFirstDay"
Case workDOW = 5: Application.Goto reference:="thuFirstDay"
Case workDOW = 6: Application.Goto reference:="friFirstDay"
Case workDOW = 7: Application.Goto reference:="satFirstDay"
Case Else: Application.Goto reference:="wedFirstDay"
End Select
End Sub

The only case statement that executes is the "otherwise". I've tried working with WorkDOW as String, also to no avail.

View 3 Replies View Related

Return Weekday? (mon, Tues,wed,thur)

May 23, 2008

Cell C1 contains the formula to return the day from the full date in B1...
I tried the formula help....they return the number...but not the day... like monday or tue...how do i do it?

View 9 Replies View Related

Autofilter By Weekday In Column Of Dates

Feb 15, 2010

I have a column of dates: 1 day per row: 01/01/10, 02/01/10, 03/01/10, etc in sequence, without gaps (European date format). The list can begin with a different start date depending on initial user input and is not always a Monday. I want the user to be able to autofilter so that it only shows dates that fall on a Monday.

Private Sub Worksheet_Activate()
Dim dDate As Date
Dim strDate As String
'disable autofilter if already enabled
With ActiveSheet
.AutoFilterMode = False
End With
'autofilter column a only, from a12, to filter Mondays
If IsDate( Range("a12")) Then
dDate = Range("a12")
strDate = Weekday(dDate)
Range("a12").AutoFilter
Range("a12").AutoFilter field:=1, Criteria1:=strDate
End If
End Sub

View 2 Replies View Related

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

Display Time Depending On Weekday

Aug 15, 2007

I have a date that is customised into dd-mmm-yy dddd eg 14-Aug-07 Tuesday in cell A1. I need help in formulating a formula to identify if the date in A1 is friday, to return "5:30 pm", else "6:00 pm" in cell B1.

View 5 Replies View Related







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