# Dates Operations

Jun 17, 2006
Does excel contain a format of : "number of months"?

Example : I have 3 cells

A1 : should contain the number of months (3 months)

A2 : should contain a date with this format (dd/mm/aaaa)

A3 : should contain A1+A2

View 4 Replies
ADVERTISEMENT
May 24, 2006

(Date1-Date2) : what's the format I should use to obtain the result in number of days.

For example :

(23/12/2006 16:30:00) - (18/11/2006 15:30:00) : It should give 35 days 1 Hour (35:01:00:00 - dd:hh:mm:ss)

How can I have this format (dd:hh:mm:ss)

View 9 Replies
View Related
May 22, 2006

Does excel contain a format of just : "number of days"??

For example : I have 3 cells

A1 : should contain the number 30 (30 days)

A2 : should contain a date with this format (jj/mm/aaaa hh:mm)

A3 : should contain A1+A2

View 3 Replies
View Related
Nov 13, 2012

I'm trying to take a vba function and add a second if clause. Here's the function:

VB:

Function getdes(DRng As Range, LURng As Range)

For Each ce In LURng

If ce.Value = DRn Then

holder = holder & ce.Offset(0, 2).Value & vbLf & ""

End If

Next ce

getdes = Left(holder, Len(holder) - 2)

End Function

I want to do something like:

VB:

Function getdes(DRng As Range, LURng As Range)

For Each ce In LURng

If ce.Value = DRn If ce.Offset (0,-02).Value = "y" Then

holder = holder & ce.Offset(0, 2).Value & vbLf & ""

End If

Next ce

getdes = Left(holder, Len(holder) - 2)

End Function

View 3 Replies
View Related
Aug 16, 2008

I have "bashed into shape" a formula that involves 2 sets of parenthesis. After progressing beyond "#REF! or #NAME errors, Excel's automated Help suggested to add a comma, and the formula functions as wanted, I thought I was "Smart".

Later, when saving the formula to my `Formula Workbook', "Order of Operations" crossed my mind. - I think this formula is backwards, but `Excel Help' only suggested the addition of a comma? Maybe the all inclusive parenthes allowed the order to be understood?

The Formula: =AVERAGE((E56:AH56),COUNTIF(E56:AH56,">0"))

Q: Shouldn't the COUNTIF occur before the AVERAGE? Did the extra Parenthis automate the order of Operation within the Parenthis?

View 9 Replies
View Related
Jun 2, 2006

I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator.

In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!

'Initializing tagrow so we can test what train we are trending'

tagrow = Cells(12, 2).Value

'Only Propylene flows need adjustment in A and B trains, calculate everything'

If tagrow = 7 Or tagrow = 8 Then

Sheet2.Calculate

Range("D15:D115").Value = Range("C15:C115").Value / 1000

End If

View 3 Replies
View Related
Mar 7, 2007

I need to have the contents of cell A1 (a 3 component list) control a formula in cell E1. If A1 is "add", then E1 should be B1 x D1. If A1 is "delete", then E1 should be B1 x D1 x .6, and if A1 is "delete ilo", then E1 should be B1 x D1 x (-1).

A1 B1 C1 D1 E1

list qty. list price of C1 item formula needed

( lookup)

View 5 Replies
View Related
Mar 5, 2014

I have a formula that looks like this:

=12*NETWORKDAYS(C2,D2)-12+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)

This formula calculated the time between two dates (Start date in C2, End Date in C3) between the hours of 06:00 and 18:00 on weekdays. This is so I can work out how long an incident has spend within the group at the time when our service should be operating.

I was given this formula by someone on this forum a while back and i was new to excel and formulas so I just took it at face value since it worked. Now I want to disect it so I can use it in other situations where similar calculations are needed. I want to try to break it up into easier to understand chunks to try to understand what it is I need to change in order to do these related calculations.

NETWORKDAYS(D2,D2)

Is this trying to distinguish if D2 is a weekday?

MOD(D2,1)*24 Is this not the same as (1-D2)*24

NETWORKDAYS(C2,C2)*MOD(C2,1) Is this not the same as: IF(weekday(C2)>5,(C2-1)*7,0)

IF(NETWORKDAYS(D2,D2),....,....) What is the comparison here? if NETWORKDAYS(D2,D2) = 0 or 1?

Is this equivalent?

=12*(

NETWORKDAYS(C2,D2)-12

+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)

-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)

)

View 1 Replies
View Related
Jun 22, 2009

I'm try to put together a set of macros that perform certain operations within a file. However I've got a bit stuck on the following, and I can't see where I've gone wrong:

View 3 Replies
View Related
Jul 28, 2007

I want to copy several separate sections on a worksheet and place these sequentially in an array, then paste these in order into another worksheet.

I have written a macro to copy and paste between the two spreadsheets, however, this is rather basic as it involves straddling between the two and performing multiple alternate copy and paste operations, as only one section can be copied at any one time.

View 9 Replies
View Related
Jan 16, 2009

I am trying to write a formula in 2003. The basic premise is below and I'm having problems getting all the senarios to work out.

Cells:

E20 = Calc'ed amount

E22 = Min amount (Sometimes equals zero - No Min)

E23 = Max amount (Sometimes equals zero - No Max)

E24 = Additional amount to add

=IF(AND(E22=0,E23=0),SUM(E20,E24),IF(AND(E220,E230),IF(AND(E20>E22,E20E23,SUM(E23,E24),IF(AND(E20E22),SUM(E20,E24),IF(AND(E20

View 9 Replies
View Related
Oct 17, 2008

I am trying to make things easier for the next user by having one command button run the macros for the other worksheets (so they don't have to switch between worksheet)

However,

I get "Runtime error '1004' Application-defined or object-defined error"

View 8 Replies
View Related
Mar 30, 2007

I need a macro that will perform a set of oprations on all worksheets in a workbook. The names and number of worksheets will not always be the same. I have found several macros that look like they will do this, but when I try to add my code they don't seem to work.

View 9 Replies
View Related
Jul 18, 2007

I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?

View 9 Replies
View Related
Aug 8, 2007

To speed up copy and paste operations in a macro, I've heard that one can replace the following:

Sheet1.Range("A1:A200").Copy

Sheet2.Range("B1").pasteSpecial

with

Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value

The second code should run faster as the clipboard is not used to store the copied info.

When I tried this, though, I didn't notice any improvement in macro running speed.

View 9 Replies
View Related
Feb 2, 2007

I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.

Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.

View 2 Replies
View Related
Dec 30, 2008

when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.

View 2 Replies
View Related
Apr 30, 2013

I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.

Pivot Table Â ABC3Row LabelsSum of DebitSum of Credit

413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012

18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26

View 3 Replies
View Related
Jun 17, 2007

I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.

View 6 Replies
View Related
Nov 8, 2012

I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.

If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.

I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.

View 1 Replies
View Related
May 3, 2009

look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.

note some are on the left and some on the right.

View 5 Replies
View Related
Oct 21, 2011

I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.

Method 1 : Using Text to Column Wizard (Excel 2007)

I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.

Then I press Control ~ on this data as shown in second view.

Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011

control ~ (overview)

Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786

2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.

Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261

2nd view - system dates are getting converted into 1911

Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911

Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).

View 4 Replies
View Related
Feb 23, 2014

Here is my set up:

A2 to BF2 is a range of dates

A3 to BF3 are sales. Days without sales are 0.00

I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.

View 3 Replies
View Related
May 14, 2009

Hi Guys, This has been bugging me for a bit now and I just can't sus it...

I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.

I have beside that a column for holidays, etc and then a another column for other events.

When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).

The formula I have been playing with (no success) is:

=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.

The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...

Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...

I have attached the spread sheet that I am working on.

View 2 Replies
View Related
Apr 4, 2008

there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.

I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.

View 3 Replies
View Related
Nov 14, 2013

I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:

=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")

It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.

View 3 Replies
View Related
Jan 22, 2014

I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.

Example:

Column A

1-May

2-May

4-May

5-May

7-May

8-May

10-May

11-May

12-May

14-May

15-May

I want to list the missing dates from this list.

View 4 Replies
View Related
Jan 27, 2006

Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.

What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc

I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.

View 13 Replies
View Related
Jan 15, 2009

Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date

But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero

Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!

View 7 Replies
View Related
Dec 9, 2013

I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.

The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column

These dates also have formula by which there are derived

-------------------------Current Ageing-------------------------

Date Range

Bucket

Amount

Percent

Start Date

End Date[code].....

I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.

View 9 Replies
View Related