# date formula

1. ### Conditional Formatting Formula for Dates

Hey Y'all, I'm trying to figure out a conditional formatting formula for cells O17-O30 that highlights date cells that are either in the past, today, or up to 3 months in the future, all relative to today's date. Does anyone know a good formula for that? Thank you! Chelsea
2. ### Target dates

Hi all, This could be a novice question but I was hoping one of you could help me. I have a report to run this morning from an excel file, I have a list of target dates and a list of dates when the jobs were actually completed. I basically only want to know which jobs didn't meet the target...
3. ### 6 month anniversary purchase dates

Hi guys, I was looking to create a formula which based on a fixed purchase date gives me the next future 6 month anniversary date of that purchase date. So if the purchase date was 31/03/2018 I would (today) want my formula to return the date 31/09/2019 The original formula I had was ...
4. ### Text string plus Date formula

Pesky formula! Can anyone see where I am going wrong please? ="Book Value as at "&TEXT((DATE(YEAR(D7)-1,month(d7),day(d7)),"dd/mm/yy")
5. ### Date Swapping Formula

Hey Guys/Gals, I have been using a date swap formula to basically swap the month and day between "/" slashes. =IF(ISTEXT(C7),DATE(RIGHT(C7,4),LEFT(C7,SEARCH("/",C7)-1),MID(C7,SEARCH("/",C7)+1,2)),DATEVALUE(TEXT(C7,"mm/dd/yyyy"))) I originally was working on the formula for sample dates such...
6. ### Formula - Value if True not returning valid year

Hi there, I have a formula which the "Value if True" result is not calculating correctly. For example, if the date in cell D7 is 30/06/18 return 2016-17 but if the month of cell D7 is December, return the year of D7 minus 1 year. The bit that doesn't seem to be working is YEAR(D7)-1 This...
7. ### Return Year End Date it falls in

Hi there, I would like to calculate a year end date, based on a changeable date. For example, say the year end date is 30 June. eg. From start date 01/12/2017 I would like it to return the year end date it falls in, so 30/06/2018. eg. From start date 01/05/2017 Returns 30/06/2017 Cell F13...
8. ### Log file where each line gets a date stamp when populated and where date is then stored as value

I have a simple log file with a couple of columns to log events. One row for each event. Every cell in a row is manually entered and there are no macro's or formula's yet. To prevent mistakes I now want to make a modification in column A... the date column. As soon as one of the other cells in...
9. ### help with a excel expiration date formula

Hi guys so I have a formula that needs to show 3 things as of today if Date is within a year ( of today) = Ok Date is within 30 days ( of today) = >30 daysDate has passed ( as of today) = Expired This is what I have and cant seem to get >30 days to...
10. ### Formulas Based on Differences between months, as text?

Hey so I cannot find the solution nor figure this out for some frustrating reason.. I have two columns that are Months. They have the month names spelled out fully. One is the original, the next is the adjusted. I want to say if the first month is later than the second, "Pulled In" if the...
11. ### Need help on Date formula in excel

Hi Everyone, I have a following table <tbody> Quarter Activity Start Date Activity End Date Budget Year Q2-2017 5th May 2017 26th June 2017 2017 </tbody> i want to populate Column No. "Quarter" with formula which will return the value as Quarter 1/Quarter 2/Quarter 3/Quarter 4 with...
12. ### 20 Days into Prior Month Formula

Hello everyone, I'm looking for a formula that will give me a date of 20 days into the last month and base it on the computer systems date. For example, today is November 3rd. I would like October 20th to be the date that is returned. I've been using =DATE(YEAR(TODAY()),MONTH(TODAY()),-1) but...
13. ### Date formula to bring back specific dates after an initial date

Hi there, I am trying to put together a formula to bring back up to 6 specific dates which are specific months after an initial date. E.g. 6 payments that are 6 months apart. Here is an example with the formulas I've used (which are probably very long winded as I'm new to this!!) and this...
14. ### Subtracting Fiscal Quarters from eachotehr

I am looking to just calculate the column in red below (Column E) in a basic excel spreadsheet. So I have the information in column A which is converted from date to fiscal quarter for column B. The same applies for column C which is converted into fiscal quarter (Column D). The end result is...
15. ### Macro with dynamic date in folder path

Hi, I'm quite new to using VBA, and already very enthusiastic! I am trying to create a macro where I am able import sheets from other workbooks automatically into my workbook. I have managed to do so with the following macro: Sub import() Dim wbCopy As Workbook Dim wsCopy As Worksheet...
16. ### IF formula to mark box with an x if after a certain date

I'm having a hard time figuring this one out. I have a date in cell A. If today is greater that 6 months from the date in cell a, how would I forumlate cell b to have an x? I want my spreadsheet to look like this: Date 6 months 12 months 2/12/16 8/5/15...
17. ### Date prior to today with conditions

Hi all, I have the following data: Column A: Projected completion date (normal format DD/MM/YY) Column B: Project status (either "active" or "inactive") I want to use conditional formatting to highlight a date prior to today but only if the project status is "active". I know the formula for...
18. ### Return Value from Entered Date

I am stuck on the final formula for a vacation workbook. I am trying to return the value in column C by referencing the date in column B and placing it next to the correct date on the Sheet listed in column A. The sheet "Dummy, Taylor". The 8 entered on 02/10/2015 from above would be on row...
19. ### Date In excel

i have one simple question. i have date in one cell, lets say "A1" i am making every month attendance and i have calender A2=01,B2=02,C2=03,D2=04,E2=05,......AE2=31 what i did here A2=A1 to return first date of month and all A2+1,B2+1,C2+1.............. for until end date. its working fine if...
20. ### Months calculation

Hi, I have two dates in excel in A1 i have 201101 which is year and month together & in b1 i have 201202 which is year and month together i need to find out months in between the two dates... please help

### This Week's Hot Topics

• Problem in vba vlookup
Dear All Master, The problem I mean is as follows : 1. I want to modify the vba code because it takes too long/very slow to vlookup in VBA code...
• VBA Vlookup
Please for help. Why it`s not works? [CODE=vba]Sub Check_equpment1_click() Worksheets("Production_program").Activate Dim ans1 As Integer ans1 =...
• Nesting INDEX MATCH into SUMIFS
I currently have several formulas pulling in a SUMIFS from a data sheet, beginning with =SUMIFS('datasheet'!\$N:\$N, 'datasheet..... but as I am...
• Extracting number from a range randomly
Hello Expert, please kindly advise what is the best method to extract numbers from a range randomly I have a series of numbers in the range of A1...
• Problem with If Formula
I am using the following formula in cell A5. I would like if N5 is blank then A5 also return blank. If N5 is 0 or is greater than zero then equals...
• Is there a way to make the day's name show up automatically in a header?
I have a form I print out every day..and in the top right custom header I have the &[DATE], which automatically gives the current date in top...