All Questions
52
questions
0
votes
1
answer
66
views
EXCEL FORMULA - Conditional format Date
I am trying to work out a formula for the below specific Critia.
If cell is blank, colour in yellow if date is 2 days away, colour in red if date has 1 day over.
I am trying to build a traffic light ...
0
votes
1
answer
157
views
I am trying to get excel to automatically insert date when another cell is changed
I have tried the following:
=IF(A1<>"",IF(B1<>"",B1,NOW()),"").
and enabled iterative calculation.
It still updates to the time when I open the spreadsheet. ...
- 1
1
vote
1
answer
349
views
Update cell value based on dates in other columns
I would like to:
Update a cell (A2) with a status. That could be a standardised list, for example: "Sent to Customer", "Customer Replied", "Task Closed".
There are three ...
- 1,692
2
votes
1
answer
916
views
Using a date in an Excel function does not work when the date comes from another cell
I have the following problem: using a date as a condition in an MS Excel function works fine, as long as I write the date in the function code, such as ">1.1.2022".
But when I write the ...
- 21
0
votes
3
answers
20k
views
Don't show 1900 on blank date
I have a spreadsheet that converts the date in one cell to a year in another. I want to pull the formula down so that it automatically shows the date in columns A and B if there is a date in column C; ...
2
votes
1
answer
22k
views
Inserting Today's Date In A Cell If Other Cell Values Are Changed or Added [duplicate]
I have a spreadsheet with a column labeled, "Status Date," in a row with several other cells, some with other dates, some with just text, and others which evaluate whether a set of ...
1
vote
2
answers
324
views
I Need To Compare 4 Dates Against Today's Date To Get A Yes Or No Result
I have 4 cells on the same row that are either blank or contain a date (YYYY-MM-DD), which I need to compare to today's date. If any one cell is blank or occurs in the past, I want the result to ...
0
votes
1
answer
79
views
get a cell in the closest row of the previous year end
I have a row in mid year which I want to get the tax value of the previous year-end row.
Sometimes it will be year-12-31 and sometime there will not be that date, but a few days before it.
So in B17 ...
- 187
1
vote
2
answers
2k
views
Finding the MAX value over a range but if one row is blank, the value for MAX must be blank
I want to do a MAX value for the latest date in my data for the "Actual" column, but, say that one cell has no date, I want the MAX (in yellow) to be blank too. Any help would be appreciated!...
- 15
0
votes
1
answer
166
views
How to Calculate the Average after a certain date
Im trying to get the average of days taken to send to college but i only want it to take in to consideration dates received after 17/05/2021.
can anyone help
0
votes
0
answers
942
views
Infinite date column in Excel
I saw an Excel spreadsheet once that had a date column that would span a couple of month down and that would be the end of the spreadsheet, but every day you open and use it, that date at the bottom ...
0
votes
3
answers
64
views
Excel data query
I'm trying to build out a budget monitoring spreadsheet. I want to pull sum of entries by category and based on the month. The data is in rows as follows and will grow each month as I download our ...
- 1
1
vote
2
answers
1k
views
Excel won't recognize timestamp in format of "m/d/yyyy h:mm:ss AM/PM"
I have a column of timestamps (strings) formatted like so:
This can be deduced to the following format: m/d/yyyy h:mm:ss AM/PM
I'm trying to get them to be formatted into dates but Excel refuses for ...
- 115
2
votes
2
answers
647
views
Excel: find the nearest (to a reference one) date for each row
I'm not an Excel expert (not by far) and I've been having a hard time finding a solution for the following problem. I have a table that represents the scheduled day of shift (A) for each employee (B) ...
- 139
0
votes
3
answers
593
views
How to find which column contains the first occurance of a number in a single row? (Excel)
I have an Excel sheet with over 100 dates as column names and hundreds of locations as row names.
There are numbers (could be any number) somewhere in each row that indicate that an event will occur ...
2
votes
1
answer
2k
views
Calculate Gregorian Julian Date from Regular Date in Excel
I am trying to get a Julian Date from a regular date (mm/dd/yyyy) in Excel. There seems to be two kinds of Julian date calculation.
One is described Here..
The other is somehow related to Gregorian ...
- 1,718
0
votes
1
answer
1k
views
Excel formula to calculate date based on 2 cells and how to copy to other cells?
Novice excel user here. Here is a simplified version of my spreadsheet:
start date end date eday duration
-21 4
12 0
begindate = "03/...
0
votes
2
answers
2k
views
Formula for Index/Match with multiple criteria WITH date range EXCEL
I am attempting to write a formula that will help pull data from column D into column H (currently populated but would be blank).
Logic is as follows: If any of the IDs in column A are in the range ...
0
votes
1
answer
692
views
Auto fill blank cells in Excel
I have downloaded sales data from webstore and the rows are itemised, but they only provide the date in one cell for the order.
I am trying to work out how I can auto fill the blank cells with the ...
0
votes
2
answers
3k
views
How to keep cell blank until date entered [duplicate]
So basically, "INVOICE DUE DATE" is the SUM of D11+7 ("Date invoice sent out" + 7 days as this is when the invoice is due). However, when there is no date inputted in "DATE INVOICE SENT OUT" the "...
- 3
0
votes
2
answers
138
views
Excel switching year and month for some cells but not others
I have been given a spreadsheet with dates that are being inconsistently formatted by Excel. Since dates were entered into the spreadsheet as mm/dd/yy (i.e. no four-digit years), Excel has assumed the ...
- 1
3
votes
1
answer
193
views
Excel: Keep cell containing date the same after being set, without macros
I have a table that looks like this:
What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same ...
- 212
0
votes
1
answer
111
views
Excel - Find the document of a given type, with the closest due date
I am operating on 2 Tables.
"Table 1" is a list of current documents that I import and want to keep unchanged. The file is unformatted in any way.
Table 2 is a list of customers that I operate upon ...
- 1
0
votes
2
answers
78
views
change 2017/2/3 to 2017/02/03
I have a column in Excel which shows date but not in date format, it is in text format and the values for those dates that have one character for the month or day don't contain a zero before them.
E....
- 1
1
vote
1
answer
364
views
How do you round a time and date back to a previous day with a formula in excel?
I am trying to sum up hours for an employee worked in one shift. The employees I'm having a problem with are on night shift. The employees entries in excel are shown as individual tickets for work ...
- 21
0
votes
3
answers
5k
views
IExcel not comparing dates properly
I have a table with two dates in the following format:
C1 Sep 21, 2017 10:22 AM
C2 Sep 28, 2017 10:09 AM
If I run the following if statement against them...
=IF(M4<L4, "Fail", "Pass")
......
- 11
2
votes
2
answers
8k
views
Multiple date formats in single column in excel
I have a csv file with a date column with dates in various forms as follows:
dd-mm-yyyy
yyyy-mm-dd
yyyy-mm
yyyy
This irregular data is in a single columns.
How do I convert all of them to the dd-...
- 162
1
vote
1
answer
860
views
Writing a formula to count how many times each date appears in a set of date ranges
Given an Excel spreadsheet with two columns:
Creation Date, Closure Date
For each date covered by all date ranges over all columns, I want to know how many records cover each day.
For example:
...
3
votes
0
answers
178
views
Excel formula to work out time elapsed between dates based on very specific criteria
I have a dataset with approx. 29,000 entries related to approx. 3000 unique individuals’ admission and departure dates to and from a service over a period of time.
I’m trying to collapse each ...
- 41
0
votes
1
answer
46
views
Excel: Use date-format only on date strings that match a pattern
I'm using Excel from Office 365
I have a date column that has three types of date strings:
string # 1: 12/9/2016 0:00 # this string has month, day, year, hours, minutes
string # 2: 201605 # this ...
- 161
0
votes
1
answer
42
views
Excel Weekday Generation
I have a list of dates in a row. I just want the row above them to tell me which day of the week the corresponding date (below) fell. For example, today (30 Jan. 2018) would output "Tuesday", because ...
- 137
1
vote
1
answer
83
views
excel and birthday format
I am new to this. I do not have a screen shot for this one.
In the region I am located the birthdays are listed as: yy-mm-dd
I have: 720415 and want to convert it to: 72-04-15.
Will following work:
=...
- 21
0
votes
1
answer
330
views
Sophisticated dates sorting in MS Excel?
There is an Excel 2016 file with data about some events, the main sheet contains an ID and a date for each event, like this:
ID Date
1 2017-10-02
2 2017
3 2017-09
4 2017-09-25
… etc.
Most ...
user628454
1
vote
1
answer
673
views
How do I add 1 month to a specific date once that date has passed?
I have an excel sheet that lists our current due date. The due dates are the same day of each month (9/2, 10/2, etc) and I want the due date to update once the old date passes so I don't have to do ...
- 23
1
vote
2
answers
8k
views
Concatenated cells return random number instead of date
I have a question about an excel formula.
I have a some fields I want to concatenate together. One of those is a date made with the formula TODAY.
When I concatenate all together it will give me a ...
- 11
0
votes
3
answers
716
views
calculate datedif and stop once column filled in
I want to calculate days on wait list (0 if the assessment date column is not filled in), calculate date from today minus the referral received date, and then stop the calculation when the date of ...
- 1
2
votes
1
answer
203
views
Excel: flaw in date calculation - hashed when formula is examined (corrected by backfill)?
NOTE: I have corrected this fault... but an answer ideally requires an explanation (if anybody can explain it great).
A formula determining the end or beginning of a quarter works perfectly.
However, ...
- 55
1
vote
1
answer
64
views
How do I extract dates from a weekly tracker?
Let's say in each hour of the day in a week I record for example the food I eat or the physical activity I do.
At the end of the week, I have a table where the rows are hours (12am-12:59am to 11pm-...
- 71
0
votes
1
answer
2k
views
Add a calculated HH:MM:SS to the Current time in excel?
I've made a spreadsheet to quickly calculate how long a 3D render will take based on number of frames & minutes per frame.
However I'm also wanting to add a calculation that shows the time and ...
- 37
0
votes
2
answers
103
views
Difference in dates
There are two dates : 01-11-2008 and 28-02-2017 (in dd/mm/yy format). I want excel to calculate the difference between these two dates and give the output in whole years.
If the difference between ...
0
votes
1
answer
107
views
Excel formula for auto fill a date
I would like to have cells F4-F23 auto fill today's date whenever data is entered into cells E4-E23. I then need to have cells I4-I23 auto fill with today's date whenever data is entered in cells H4-...
- 1
3
votes
3
answers
4k
views
Formula to set date to nearest previous (most recent) 25th of the month
I need a couple things here, but I think they may be similar formulas:
I have a list of dates in an Excel file (Column A). I'd like to create a 2nd list (Column B) that will have a formula that will ...
- 37
0
votes
1
answer
156
views
Date formula in excel regarding calendar
I need to fill in a Day and Date column in an Excel table to create a one month calendar:
Select a year: 2016
Select a month: September
-------------------------
| Day | Date |
|---------...
- 39
0
votes
1
answer
99
views
date calcutation in excel based on other dates
=TODAY
date A date B date C
I need formula which will calculate me date B.
I.e. If between date A and =TODAY has passed more then a year and if date C is empty then date B will be same as =...
- 39
0
votes
1
answer
272
views
Excel - Multiplying daily values for each week
What I have: over 200 rows, each with three values, each for a unique day. Only values for workdays are listed, so sometimes there may be days or even whole weeks missing from the list because of ...
- 5
0
votes
1
answer
269
views
Excel: conditional formatting with with complex function AND
I have a sheet that looks like this:
So I have a list of documents to create, and I mark "1" on the column with the month in which I have created them.
Now I've been asked to put "1" also as a ...
0
votes
1
answer
20k
views
Excel Pivot Table - How can I return a date in the row value?
I have raw data that includes hundreds of employees who have completed many different training courses and I am needing to insert a pivot table that shows the course name as the column labels, the ...
- 1
0
votes
1
answer
121
views
Grab cell data and use in "IF" statement
Is it possible to grab a cell containing a date and paste that date in an "IF" statement containing text, similar to the below:
=IF(OR(I1=TRUE,J1=TRUE),"Date:___________________",IF(AND(I1=FALSE,J1=...
- 139
1
vote
1
answer
665
views
How to count months in intersection of two date ranges [closed]
I need to calculate the number of months in the intersection of two date ranges.
For instance, in Column A I have start dates of employment
12/1/1998, 1/1/2003, 2/1/1995
and in column B I have ...
0
votes
1
answer
251
views
Looking for a formula(s) that will return the next date (higher than the starting date) from a row of dates, then the next date from that one
Worksheet 1 lists all of the "legs" flown by our aircraft. The leg or legs for each day would then go to Worksheet 2 for Day 1, Worksheet 3 for Day 2, and so on. Legs are listed in columns starting ...
