Service Date changing

  • Thread starter Thread starter Pammy
  • Start date Start date
P

Pammy

I have a spreadsheet:
N R S
T
Service Dates" , "Hours Worked 2008" Days answer Rounded
Ans
5/4/2004 551.00 =R3/8 68.875
=roundup(S2,0) 69

Now I need to add the 69 to the Service Date to get a new date, so in column
U would the formula be = N2+T2

7/13/2004 is the answer I get, is this the correct way?
 
What are you trying to do?
Adding 69 to your date adds 69 days to your date. What are you trying to
calculate? What result do you expect you should get?
 
I guess I was wondering if you just add a number (example 69) to a cell that
is formatted with a date (example 5/4/2004), give a correct answer. I wasn't
sure that it calculated based on the number of days in a month with the
formula I was using. =date+days
 
If you are trying to add 69 days to the date of 5/4/2004, then yes, your
formula is fine. If, however, you are wanting to just add working days, you
might wanna use the function WORKDAY. The workday function has the
flexibility of not just skipping weekends, but also holidays if you like.
For example:
=WORKDAY(N2,T2)
if you don't want to worry about holidays
or, say you enter the dates of holidays on a separate tab, and for example
in column A of Sheet3, just starting in row 1 until, well, whenever, then
your formula could be:
=WORKDAY(N2,T2,Sheet3!$A$1:$A$100)

This would add 69 working days that aren't holidays to your original date
(skipping the standard Memorial Day and July 4).

Essentially, excel stores dates as whole numbers counting from Jan 1, 1900
(or Jan 1, 1904 depending on your excel settings), but that isn't the
important part. It stores times as fractions of days. For example, if you
were to enter a date and time into a cell such as 11/11/2008 12:00 PM, and
then change the format of the cell from date/time to general (or number with
decimal), then it would display 39763.5 (based on Jan 1, 1900). Since noon is
exactly half the day, if you entered 4:00 pm instead, it would be 39763.66666
(repeating).

Hope this helps.
 
Back
Top