# Excel Formula - Dates

Discussion in 'Business Applications' started by sk_rizwan, Apr 17, 2012.

Not open for further replies.

Joined:
Apr 17, 2012
Messages:
3
Hi,

Need Urgent help. Need a formula, explained in detail in attachment and trying to explain here

Column A (Inv Value) Column B (Col.Date) week1 (14/4/2012 - 18/4/2012)
4500 1/5/2012 0 (formula)
225000 23/4/2012 0
55393 17/4/2012 55393 (formula)

* Column A and B are Data field from where we will take data.
* Column C and D are the fields where i need to put formula

**** If column C date range is in Column B date, then put the value of Column A
I can split Column c in two cells with start date and end date if needed (then merge remaining cells)

Appreciate your help and support on urgent bases to solve my issue. < removed email address - etaf moderator >

File size:
10.9 KB
Views:
38
2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,879
First Name:
Wayne
i have removed the email address to avoid spam
also we only provide support in posts and not via email or chat programs etc

I will have a look at the spreadsheet - this is something I carried out a couple of weeks ago for a project gantt chart -

3. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,879
First Name:
Wayne
OK - so I changed the date structure
i had to change the dates in column C as 1) they where not recognised as a date and some where out the range of the sample
so i retyped them in - not sure why that is - you may need to check the dates

if you have a lot of dates - try this
Put
=value(c2)
in a cell away from your data - say AA2
copy down to cover all rows with data
this should result in a number
now
Copy the range AA2 - AA#
to C2
paste special
value
into C2
now format as Date
and that should fix the date issue - worked on my spreadsheet OK

I change the header - so that you just have the week starting date and uses cell in last week +7 to work out the next week date

now the formula uses AND to test if the date in column C is less than the next week and greater or = to this week
then i just use an if

so in D2 =IF(AND(\$C2<E\$1,\$C2>=D\$1),\$B2,"")

i have attached the spreadsheet - sheet 2 with the calculation

I think you may need to retype all the collections dates again

have a look see what you think and ask any questions

#### Attached Files:

• ###### Dates and Values_etaf.xlsx
File size:
12.6 KB
Views:
77

Joined:
Apr 17, 2012
Messages:
3
Dear Etaf,

Thanks for the formula. It is working fine but it seems problem with date formats as you said.

Actually, I am exporting this data from another source. Now, the problem arises with date formats. Even i check your solution copying =value(c2) then paste special and formatting dates but it is Not working with me. I have hundreds of rows and need to fix this date formatting. I will try and could you please also find a way.

Really appreciate your immediate response that you have provided with formula.

Thank you,
Rizwan.

5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,879
First Name:
Wayne
working OK for me and on your original data - if i do a =value(c2) I get numbers for all the dates
BUT
when I copy back into the original C2 cells and down - only the first three display a date and so i change to format as date and it works OK

exactly what are you doing step by step

perhaps post a spreadsheet with more of the dates in

6. ### Garf13LD

Joined:
Apr 17, 2012
Messages:
455
Just make sure column C is in date format.
I have include formula in Col J to flip reverse dates.
Make sure J1 is not deleted.
Just copy from J1 to the last column and then paste value back into Col C will do.
If ur dates are in mixed formats, then u have to find a way to control user input.
Upload more dates so that I can come up with solutions.

#### Attached Files:

• ###### Dates and Values.xlsx
File size:
12.1 KB
Views:
43

Joined:
Apr 17, 2012
Messages:
3
Dear,

Please find the attached sheet that has two columns for dates. i will get the DATA in this format.

When i select =value(C3) it will work, but when i do for rest of the fields it will not work. I think the whole problem is about formats. Work on the attached sheet and you will know in detail.

Thanks for your help and support.

File size:
18.7 KB
Views:
30
8. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,879
First Name:
Wayne
try this
=IF(ISERROR(DATE(YEAR(C2),MONTH(C2),DAY(C2))),"",(DATE(YEAR(C2),MONTH(C2),DAY(C2))))
put it in a cell some where on the sheet
and then
copy down to match all the rows you have
then select all the rows in that column
copy
paste special
value
back into c2
format cell to date
works for me

File size:
187.6 KB
Views:
33
9. ### Garf13LD

Joined:
Apr 17, 2012
Messages:
455

I'm seeing this, are you?
I assume the first date in B2 is 1 Jun 2009

Is your short date format dd/mm/yy or mm/dd/yy or some other else?
Try give us in CSV or print screen.

File size:
70.3 KB
Views:
217

Joined:
Apr 17, 2012
Messages:
455

File size:
24.8 KB
Views:
27

As Seen On