# date formula

1. ### 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...
2. ### 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 ...
3. ### 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")
4. ### 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...
5. ### 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...
6. ### 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...
7. ### 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...
8. ### 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...
9. ### 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...
10. ### 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...
11. ### 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...
12. ### 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...
13. ### 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...
14. ### 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...
15. ### 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...
16. ### 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...
17. ### 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...
18. ### 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...
19. ### 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

Hi, I am trying to build a tax calculator/forecasting tool. I need it to be relevant and up to date. In summary Cell A1 is a date entry box. This is always my starting point. There are a number of transactions that are linked to a quarterly and bi annual cycle and I cannot come up with a...

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...