# Subtracting Dates Results In A Big Number When A Date Field Is Blank

Jul 11, 2009How can I change the ....

View 14 RepliesHow can I change the ....

View 14 RepliesADVERTISEMENT

I have three search boxes in XL , two are search from and to dates and the other is search be ref.

I can currently search by dates and ref but would like to show all records in the specified dates if the ref field is left blank.

I am using the query writer and referencing back to cells in XL. I don't use the wizard or SQL. I have tried many options and think I should be using a OR statement with a wildcard but can't seem to get the result, does the wildcard just show the blank cells in the actual data?

I wish to subtract the date 11/18/1877 from 11/16/1968 to get a result showing the number of intervening days but excel does not want to do it.

When I take 11/18/1900 from 11/18/1968 and format the result to get a number with no decimals the result is 24835 as it should be; but it does not seem to work for dates earlier than 1/1/1900.

I have some intraday date that looks like this, hope the formatting comes thru when i post this....anyhow, the second column is TIME (ie 15:25 15:30, etc...5 minute time intervals for SP500 stock data. I want to have a column that simply subtracts row 2 column b (time column) from row 1 column b.....when i do this i get a #value....I know this must be possible to get a result i need....ie 15:30 - 15:25 = 5

Do i need to reformat the TIME column into something different? ....

I created this formula =G2*2.9%+.3

what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88

the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30

How do I prevent the formula from calculating if the G column is blank?

In A1 I have this: $9.50.

In A2 I want to display the result of multiplying A1 with 10% and subtracting the result from A1.

I've tried this in A2: =SUM(A1*10%)-SUM(A1)

But it displays a negative number: -$8.55

I need it to display: $8.55

Here's an example of what i want to do

A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3

I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.

I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?

View 3 Replies View RelatedI want column S to show today's date if any cell is modified within that row.

Private Sub Worksheet_Change(ByVal Target As Range)

Cells(Target.Row, 19).Value = Int(Date)

End Sub

and it works, I get today's date in that row. But when I do a test on the date, (=ISNUMBER) I get false which means I can't check that date with a macro.

I need to have a cell with a date in it then the next cell needs a formula to caluclate how many days it has been since that date???? I'm a real novice with excel.

View 9 Replies View RelatedI have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.

View 5 Replies View RelatedI have a spreadsheet with a column full of dates

I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.

Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..

There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!

What would the formula be for subtracting two dates to get the numbers of years, months? I cannot get my formula to work.

Also, can Excel take a telephone number such as (555)-555-5555 can place the phone number in another field such as 555-555-5555?

On my worksheet I have a date of birth formated yyyy-mm-dd. On my form, I want to be able to identify anyone younger than 16. I tried subtracting from today's date on the worksheet, but that didn't work too well for me.

View 2 Replies View RelatedI have a weekending date. I would like to subtract X amount of weeks from this date and end up with the weekending date less the weeks I subtracted.

View 10 Replies View RelatedI am trying to subtract 45min from the time 00:05, but the result i am getting is 0:40 which is wrong.

I am using below formula.

=TEXT(ABS(B3-$A$3),"-h:mm")

Actual timeTime minusResults00:0500:45-0:40

1) To be able to get the current date in the format of 3/10/2008

2) To be able to take dates from 2 cells and get an integer value for the number of days between the two dates

I'm attempting to write a formula that will allow me to determine the number of days between two dates but to leave the cell with a zero in it if either of the first two date cells are blank or have N/A in them. Here's what I have so far:

=IF(AND(K5="N/A",L5="N/A"),0,L5-K5)

This will give me a zero in the cell if both date cells are blank or will give me the days between if not. How do I get it to also put a zero in the cell if only one of the date cells is blank?

I attached an excel file for an example. Basically I am calculating on time shipping, but I want don't want Saturday and Sunday to coun't against us.I need G5 to equal 3 days. Right now when I subtract E2 from F2 I get 5 days. But we only work M-F, and Saturday and Sunday shouldn't count against us. How would I subract the days automattically, so it takes out Saturday and Sunday?

View 10 Replies View Related I have a minor assignment at work. The manager has given me a sheet that lists each employee's start date, and he wants to know what percentage of them have worked here for more than 30 days, 60 days, 90 days, etc. I know a long way to do it, but there's several hundred names, and there must be a quicker way. Is an array formula the answer? I need a non-VBA answer, but I'm not opposed to learning a VBA method (no pun intended).

Does anyone know why I get a type mismatch on the following? I am trying to subtract one time from the other to get the total hours worked.

Sub WorkedHours()

Dim vStart, vEnd As Date

Dim vHours As Integer

vStart = "11/09/06 22:00"

vEnd = "11/09/06 23:30"

vHours = (vEnd - vStart) * 24

End Sub

I have a spreadsheet of clients' body weights each week which goes in a column and to the right of it I have a "diff" which I want to show weight gain/loss.

So for a client with weigh-ins the week of 10/1, 10/8, and 10/15 the columns will be:

10/1(V1)--Diff(W1)--10/8(X1)--Diff(Y1)--10/15(Z1)...and so on.

If I have weigh-ins for each week, no problem. I would just have to: =SUM(X1-V1) to get the change in weight from the week of 10/1 to 10/8. Say the client weighed 200 on 10/1 (V2) and 195 on 10/8 (X2), the difference (or "diff" (Y2)) would be -5.

But let's say the client didn't weigh-in on 10/1. The difference in weight is going to be 195. Or if they weighed-in on 10/1 but didn't on 10/8 it's going to read -200.

So I have two cells (say V2 & X2) that if one of them is blank, I want the "diff" to read "N/A" or at least leave the cell blank.

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.

The information is currently part of a Table in Excel 2007 that has about 500 records.

IdeasList

*

J

K

2

Status

Month Completed

3

In Progress

*

4

Complete

Jul-13

5

*

*

6

*

*

Data Validation in Spreadsheet

Cell

Allow

Datas

Input 1

Input 2

J4

List

*

=ValidationList_Status

*

K4

List

*

=ValidationList_Months

*

I am trying to calculate the length of time of employment. I am looking for a formula to subtract the date of hire from the date of termination.

My current example is:

5/24/12 - 11/3/10 = 568

There is a problem with my formula as the answer of 568 is currently meaningless. How do I convert this?

Using Excel 2007

I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.

View 2 Replies View RelatedI am trying to take the year from today's date then subtract away a date from a referenced date in a separate workbook. Then I want to put this result into an IF statement. For example if the result is between the number 1 and 4, display 1, if the result is between 5 and 9, display 2, etc. Right now, I'm simply trying to reference the separate workbook for the date and subtract that from today's date, but I'm stuck because I only get the date's serial number back.

View 1 Replies View Relatedin coverting these fields into a date field.

Example 91306 to 060913

I have encloed the file.

I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.

Example

See Attached

I am using excel 2003 and I am trying to subtract 20 days from the date in cell A3 in order to give me a date for me to order materials. However all I get is #NAME? in the cell.

View 4 Replies View RelatedIf I have a date in Cell A1, what formula do I need to put in cell B1 to subtract 6 months.

eg If A1 = 14/08/2007 I want B1 to read 14/02/2007 and not 12/02/2007 which it reads when subtracting half a ywears worth of days

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