Hej,

Jag har en fakturamall i Word med fakturaadresserna i en Excel-fil. Jag samsorterar dokumenten och skulle vilja att förfallodatumet räknas ut automatiskt.

Jag skriver dagens datum i en kolumn i Excel och i kolumnen bredvid tänkte jag att den autoamtiskt ska lägga på 10 dagar. Finns det någon funktion för detta i Excel? Ett alternativ skulle kunna vara att göra det i Word-dokumentet i stället, jag är öppen för alternativa lösningar.

Helst vill jag inte behöva göra ett vbscript, helst vill jag ha en funktion, motsvarande dateAdd i ASP.


Tack på förhand
Ulf

Testa om detta ger något: http://www.contextures.com/xlfaqDat.html

Dates and Times
Many Excel questions are about dates and times. Chip Pearson's web page:
http://www.cpearson.com/excel/datetime.htm
will give you understanding of how this works in Excel , and it has lots of useful samples.
Here are a very few common questions:

Time Calculations
How do I add times together?
How do I subtract time?
How do I sum the time data in the format: 5:20, 12:02, 20:12 etc. to get 50:07?
I'm adding cells with seconds, i.e. 25, 50, etc... the result I'd like is 1:10
I need to calculate 0:45 minutes at 120 per hour - with an answer of 90.
Date Calculations
How can I enter the date into a cell so it doesn't change every day?
When I copy and paste dates, they end up one day/four years wrong.
How do I add 3 months to a date?
What worksheet functions would emulate EOMONTH; last day of month?
Excel thinks 1900 is a leap year. It's not


1. Time Calculations
How do I add times together?
Just add together like any number (=A1+A2+A3). Use custom number format [h]:mm in the result cell to prevent rollover at 24 hours

How do I subtract time?
Just subtract, like =B1-A1. Use Calendar format 1904 if you need to display negative results; Tools/Options/Calculation and check the 1904 Date System checkbox. Note that 1904 calendar will offset all dates by 4 years 1 day, so be careful.

When I try to sum the time data in the format: 5:20, 12:02, 20:12 etc. I get the value that is the real sum minus N*24, e.g.. 2:07 instead of 50:07.
Use the custom number format [h]:mm to prevent rollover at 24 hours



I'm adding up a large number of cells with seconds in them, i.e... 25, 50 47, etc... the result I would like is 1:10, 1:50: 2:03

Since XL stores times as fractions of days, in order to convert integers into times you need to divide the sum by (24*60*60) or 86,400

I need to calculate a column with hh:mm (formatted for TIME) against a hour rate. So 0:45 minutes needs to be calculated against 120 per hour - with an answer of 90. Now it says 3.75?
1 is a day; 24 hours. So one hour is 1/24. =A1*B1*24 will bring the desired result. Format result cell as currency or number (it tends to pick the date format by default).


2. Date Calculations
How can I enter the date into a cell so it doesn't change every day?
Press Ctrl + ; (hold the Ctrl key while pressing semicolon.)
Press Ctrl + : to enter the time.

When I copy and paste dates, they end up one day wrong.
When I copy and paste dates, they end up four years wrong.
One workbook is using 1904 calendar, the other one 1900 (in the first example, years are not displayed, but definitely present). Go menu Tools>Options, Calculation and make them equal, preferably also correct if you know what the dates was supposed to be.

How do I add 3 months to a date?

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))

Could someone give me the series of worksheet functions that would emulate EOMONTH; last day of month?
The last day of the month equals the zero'th of next month for some strange reason: =DATE(YEAR(A1),MONTH(A1)+1,0)

Excel thinks 1900 is a leap year. It's not.
Yes it does and it's not. The following MSKB article explains the reason:
XL: Excel Incorrectly Assumes 1900 Is a Leap Year
http://support.microsoft.com/?id=181370

FAQs collected by Harald Staff, Excel MVP
Posted by Debra Dalgleish, Excel MVP

Tack, jag provade aldrig den enklaste lösningen, att addera precis som vanlig :-)