However, I am not sure how to account for a potential break in service time such as an occasion where someone left the industry and returned years, months, or days later. Curious is there is a formula to subtract from the previous output a cell indicating a known break in service. Have not found anything to help online
Thank you
-
Alexander Trifuntov (Ablebits Team) says
2023-06-16 at 8. 25 am
To find the duration in years, months, and days for two time periods, try the DATEDIF formula
=DATEDIF(0,D4-D3+D2-D1,”y”)&” Years “&DATEDIF(0,D4-D3+D2-D1,”ym”)&” Months “&DATEDIF(0,D4-D3+D2-D1,”md”)&” Days”
D1-start D2- end D3 -start D4 – end
This should solve your task
hidayat ullah says
2023-06-05 at 7. 32 am
i want logbook of specific month with saturday and sunday being labelled as weekend with some specific color
-
Alexander Trifuntov (Ablebits Team) says
2023-06-05 at 11. 51 am
You can find the answer to your question in this article. How to conditionally format dates and time in Excel with formulas and inbuilt rules
Sew says
2023-06-01 at 2. 32 pm
Hi, In my company a person gets a half day leave when he completes a month at the workplace. For example. If someone joins in March 10 and by April 10 he will be eligible for a half day leave. If he doesn’t take that leave by May 10 he will have a full day (with April 0. 5 + May 0. 5). Can anyone suggest a formula to add 0. 5 by the end of the month automatically?
-
Alexander Trifuntov (Ablebits Team) says
2023-06-01 at 3. 01 pm
Hi. You can try calculating the number of months from the start date using the DATEDIF function
Read more. Excel DATEDIF function to get difference between two dates
=DATEDIF(A1,TODAY(),”m”)*0. 5
Matt B says
2023-03-25 at 12. 07 pm
I fitted a cubic polynomial trendline to a dataset with dates on the x-axis and power on the y-axis. I (using a website) solved for x at a point on the plot. I converted the same date to a number using DATEVALUE assuming that is what the trendline was using for the polynomial equation. But while x solved to be 24505. 24, the DATEVALUE yielded 45004. Any idea what is going on here?
-
Alexander Trifuntov (Ablebits Team) says
2023-03-27 at 8. 41 am
Hi
DATEVALUE function cannot convert date to number as you write. Read more about DATEVALUE here. How to convert text to date and number to date in Excel
Kalpesh says
2023-03-22 at 7. 41 pm
I have a date format as 20211123 and I want to convert it as 11-23-2021. Once I try changing it via date option in format fields option I am getting an error as #####. I have tried changing my system time but still it is not working
-
Alexander Trifuntov (Ablebits Team) says
2023-03-23 at 8. 54 am
Hi
Use the MID function to extract the desired digits and insert them into the DATE function. Set the individual date format according to these instructions. How to change Excel date format and create custom formatting
Try this formula
=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))
Rika says
2023-03-14 at 6. 40 pm
Hello
Is there a way to put a deadline on =today() so that it stops updating when it reaches that date?
isha says
2023-03-14 at 11. 32 am
I have a formula for count Age that is
Year(Today())-Year(Date) this is right formula but when i use this formula like Year(Today()-Year(Date)) so, that formula is given number like 2,017 so please tell me
-
Alexander Trifuntov (Ablebits Team) says
2023-03-14 at 12. 01 pm
Hello
We have a special tutorial on this. Please see – How to calculate age in Excel. from date of birth, between two dates
In your second formula, you subtract the days from the current date
Anh Trần says
2023-03-06 at 6. 14 pm
Hi Alex,
I want to convert the days before and after a date into 0 and 1, could you please help me with this?
Mike says
2023-02-14 at 9. 04 am
Hi Alex,
Is there any way to get the present time by entering the time zone in google sheets?
Waiting For your reply, Thanks
Vanessa says
2023-02-09 at 3. 33 pm
I have a received date, and the case should be completed within 30 calendar days
For example. Received 02/09/2023 – Due date ( 30) should be 03/11/2023 Formula = received date + 30
But 03/11/2023 ( is Saturday, or Sunday, or a Holiday
I need to calculate the due date, but in case the due date is Saturday, Sunday, or a Holiday, the due date should the prior business date
For example the due date will be 03/10/2023
-
Alexander Trifuntov (Ablebits Team) says
2023-02-10 at 9. 01 am
Hi
To determine the day of the week in 30 days, use the WEEKDAY function
=IF(WEEKDAY(A1+30,2)>5,A1+30-7+WEEKDAY(A1+30,2),A1+30)
I also recommend that you pay attention to this manual. Calculating weekdays in Excel – WORKDAY and NETWORKDAYS functions
Mike says
2023-02-07 at 9. 09 am
Hi Alex, I Hope you are having a Great Day
Is there any way to use countifs function with multiple criteria but at least one should be match. can you solve it for me please?
Thanks
Ati says
2023-02-03 at 12. 59 am
is there a formula for the value how many days passed from the current month?
I mean 8th of February would be 8
14th of February would be 14. etc
Amy says
2023-01-31 at 2. 32 am
Hi, I have a date, written as 02-Feb-23 in excel. I want to remove 4 weeks and return the date on the closest Tuesday to 4 weeks prior to the original date
Is there a way to do this?
-
Alexander Trifuntov (Ablebits Team) says
2023-01-31 at 10. 31 am
Hello
If I understand your task correctly, the following formula should work for you
=WORKDAY. INTL(A1-14,-1,”1011111″)
You can learn more about WORKDAY. INTL function in Excel in this article on our blog. Calculating weekdays in Excel – WORKDAY and NETWORKDAYS functions
jmz says
2023-01-30 at 1. 55 pm
Good day
Hello sir Alexander, may I ask for help on how to make formula like this
for example
today’s date Jan. 30, 2023 but I want to put this on sheet like 01/30/2023. how to do this 01/30/2023? Thank you so much
-
Alexander Trifuntov (Ablebits Team) says
2023-01-30 at 3. 04 pm
Hi
To change the date form, use this guide. How to change Excel date format and create custom formatting
I hope I answered your question
-
jmz says
2023-01-31 at 3. 37 pm
thank you so much sir for the reply, I just need to change the location. ty again
-
Atai Eshiet says
2023-02-10 at 4. 48 pm
Có thể bạn quan tâm
- Thi vào 10 Hà Nội 2023
- Chính sách nghỉ phép ở Karnataka 2023 là gì?
- Quốc gia nào có dân số đông nhất 2023 danh sách?
- Ruwah 2023 rơi vào ngày nào?
- Kết quả giếng Ấn Độ 2023
I have start date as 01-Jul-10 and end date as 28-feb-23 i need the number of years and balance of weeks
-
Alexander Trifuntov (Ablebits Team) says
2023-02-13 at 6. 54 am
Hi
The following tutorial should help. Excel DATEDIF to calculate date difference in days, weeks, months or years
Mike says
2023-01-26 at 2. 44 pm
Hi Alex,
Is there any way to use the unique function to return multiple ranges in one column?
If yes then please do the needful
Thanks
-
Alexander Trifuntov (Ablebits Team) says
2023-01-27 at 8. 02 am
Hello
The UNIQUE function can only operate on one range of data. You can combine multiple arrays or ranges vertically into a single array using the
I hope it’ll be helpful
-
Mike says
2023-01-27 at 11. 19 am
Hi Alex, Thanks for the help,
Also, I want to know if there is any way to do multiple lookups and return various values from different tables and ranges
I am looking forward to hearing from you
Thanks
-
Alexander Trifuntov (Ablebits Team) says
2023-01-27 at 2. 14 pm
Hi
If I understand the question correctly, check out this article – VLOOKUP across multiple sheets in Excel with examples
mahendiran says
2023-01-20 at 8. 59 am
hai,
My cell value is 31-Dec-10. , i need to convert into dd/mm/yyyy format
-
Alexander Trifuntov (Ablebits Team) says
2023-01-20 at 10. 51 am
Hi
I recommend reading this guide. How to change Excel date format and create custom formatting
If your date is written as text, convert the text to date in any of the suggested ways
You can convert more than 500 combinations representing dates in text format to regular Excel dates using Convert Text to Date Tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free
Mayur says
2023-01-13 at 3. 43 pm
I want 2 different formula to get the desired date from starting point of date,
For example,
Formula 1. FYE = 30/09/2022 (starting point of date) and i want first day of the 5th month from the FYE 30/09/2022 = 01/02/2023 (desired result of the formula)
Formula 2. FYE = 30/09/2022 (starting point of date)and i want that my validity period should end in 16 Months from FYE and date should be displayed the last date of the 16th Month) = 31/01/2024 (desired result of the formula)
Thank you in advance
-
Alexander Trifuntov (Ablebits Team) says
2023-01-16 at 7. 30 am
Hi
If I understand your task correctly, the following tutorial should help. How to add and subtract dates, days, weeks, months and years in Excel
For example,
=EOMONTH(A1,4)+1
Sylvester says
2023-01-10 at 3. 32 pm
I want a month cycle as 21st to 20th count as months 1st date to 30 or 31st whichever months come in, is there any custom formula for this?
Sylvester says
2023-01-10 at 3. 30 pm
Hi Sir,
I want to count 21st date as a 1st date of the month, and also same for the weeks can you do a custom formula for this?
Ryan says
2023-01-10 at 2. 48 am
Hi Alex,
I have a finance sheet comprised of columns such as
•payment due date, 3rd of each month (column A)
•payment amount due (column b)
•actual date when paid (column c)
•actual amount paid (column d)
•etc
Each row is a successive/consecutive month throughout the duration of the contract. I’m seeking assistance with both a formula and conditional formatting (hopefully without inputting 50+ duplicates for the latter)
I have a cell specified to tally the # of missed payments via column D with =COUNTIF(range,”<1”). I now need to tally the # of late payments on/after 4th of the month. I’ve tried to utilize a full column range, with multiple criteria attempts of cell value/date in column C being later than cell value/date in column A
The conditional formatting is to highlight the rows of both. late payments, amber & no payment, red
What’s your advice?
-
Alexander Trifuntov (Ablebits Team) says
2023-01-10 at 8. 46 am
Hello
To count the number of missed payments, use the SUMPRODUCT function
=SUMPRODUCT(–(C1. C10-A1. A10>1))
I hope it’ll be helpful
Victoria says
2023-01-06 at 5. 07 am
I’m trying to create a spreadsheet that will track how much vacation time an employee has at the current moment based on earning 0. 219178082191781 hours a day or 1. 534246575342466 a week. I also want the employee to be able to enter hours taken any day they take time off (this part I know how to do). I just do not know how to create a formula that well add the vacation time earned daily so they don’t have to go in each paycheck and update their available hours. Hopefully this makes sense
-
Alexander Trifuntov (Ablebits Team) says
2023-01-06 at 12. 04 pm
Hi
I don’t quite understand what you want to do, but maybe this article will be helpful. How to do a running total in Excel (Cumulative Sum formula)
Alex says
2023-01-04 at 10. 49 am
Hello,
I have the below formula, but in this year (2023) it doesn’t work, if change my system time to 2022 then it works, please help thank you in advance
=IF(MONTH(AB2)=MONTH(TODAY()-1),1,IF(MONTH(AO2)=MONTH(TODAY()-1),1,IF(MONTH(AL2)=MONTH(TODAY()-1),1,0)))
John says
2022-12-27 at 3. 05 pm
When I enter a line in Excel, the Today() formula will give, todays date, which is ok, but if I open the Excel sheet tomorrow, it will gives tomorrow’s date for every line. What can I add to the formula, to keep the previous dates, at the date of entry and if I enter a new line, it will give automatically today’s date? Thanks for all your help in advance,
-
John says
2023-01-11 at 1. 58 pm
Not sure you understand me well, but I also tried the following formula. =IF(a2″”, IF(b2=””, TODAY(), b2), “”), but Excel is not accepting this formula. The plan is as follows. if I enter something at cell A2, it should give todays date and it shouldn’t update the date it should remain the same), if I open the excel sheet tomorrow again, it should give tomorrows date for that new line. Hopefully you have an idea to resolve this. Thanks for your help in advance,
imran says
2022-12-09 at 1. 10 pm
Dear Sir,
When I combine data from 3 columns 125 25. 02. 2022 P. S. Sadar into one column output result comes just like that125 dated 222222 p. s Sadar
I want result 125dated 25. 02. 2022 P. S. Sadar
-
Alexander Trifuntov (Ablebits Team) says
2022-12-12 at 7. 29 am
Hi
To combine a date with text, convert the date to text using the TEXT function. For example,
TEXT(A1,”dd. mm. yyyy”)
-
Alexander Trifuntov (Ablebits Team) says
2022-12-12 at 7. 29 am
Hi
To combine a date with text, convert the date to text using the TEXT function. For example,
TEXT(A1,”dd. mm. yyyy”)
imran says
2022-12-09 at 1. 04 pm
Dear Sir,
1. I have the date format in Excel 02/25/2022 I want to get it into 25. 02. 2022
Pamela Venturina says
2022-12-06 at 12. 40 pm
What formula to use if I want the have the date of the beginning and end of the year to appear automatic and permanent formula to use without changing it yearly
-
Alexander Trifuntov (Ablebits Team) says
2022-12-06 at 12. 48 pm
Hi
With the DATE function, you can display the start and end dates of the year. What formula you’re talking about, I can’t guess
DATE(YEAR(TODAY()),1,1)
DATE(YEAR(TODAY()),12,31)
Denny says
2022-12-05 at 3. 51 am
How to make the formula for Year and month automatic , than showing the format is YYMM only
thank you for the advise,
-
Denny says
2022-12-05 at 4. 06 am
example label need to put in 2212 only,
but i used the formula =year(today())&month(today()) result is 202212 ,
202212 need to convert to 2212 only how the formula
-
Alexander Trifuntov (Ablebits Team) says
2022-12-05 at 12. 19 pm
Hi
If you need to show only the month and year in the date, use a custom date format “YYMM”. For more information, please visit. How to change Excel date format and create custom formatting
Giyas says
2022-11-24 at 4. 58 pm
In my Gantt chart project start date is 1-Jan,22 8 Am
Every day my project runs 8 hours (8am- 4pm)
How can I calculate the end time with day & time?
Like 1 day 3 hours need to end project
in this way output show 3 jan, 22 11am
we need to follow the weekend also (like 2 Jan 22 weekend)
in this way output show 3 jan, 22 11am
at this moment use the below formula but for decimal day end day cant output the correct answer
=WORKDAY. INTL(U21-1,S21,”0000000″,$F$1005. $F$1166)
-
Alexander Trifuntov (Ablebits Team) says
2022-11-25 at 9. 09 am
Hi Giyas,
You can specify a custom weekend for the WORKDAY. INTL function as described in this article
-
Giyas says
2022-11-25 at 9. 52 am
@alexander
workday. int work properly but i want to calculate day with our for every project as per my estimated day & hour
In my Gantt chart project start date is 1-Jan,22 8 Am
Every day my project runs 8 hours (8am- 4pm)
How can I calculate the end time with day & time?
Like 1 day 3 hours need to end project
we need to follow the weekend also (like 2 Jan 22 weekend)
in this way output show 3 jan, 22 11am
another demo in below
Daily Target Estimate DAYS Start Date End Date
3400 1. 96 11-Nov-22 12-Nov-22
3400 1. 90 13-Nov-22 14-Nov-22
3400 0. 71 15-Nov-22 15-Nov-22
need to calculate time / hour with day
-
Alexander Trifuntov (Ablebits Team) says
2022-11-25 at 10. 19 am
Hi
If I understand the problem correctly, use the WORKDAY function to add working days, including holidays. Then calculate time and add hours
=WORKDAY(A1,1,C1) + (A1-INT(A1))+3/24
Hope this is what you need
Joyal James says
2022-11-18 at 2. 37 am
I would like to make a Task-completion list with “Date of Completion” in it
For that, if 3 of my sub-activities are completed in each task, the Status column will automatically show “Task completed”
Then i would like to have the date on which it became ” Task completed” in the next column. Ho to do that. ?
I tried but How do i get that date to remain like that forever. I used =IF(D1=”Task Completed”,TODAY(),””) but this gave me changing dates based on the day i opened the excel file. I need the date to be fixed once the cell becomes “Task Completed”
Pls help
Gareth G says
2022-11-14 at 10. 23 am
Hello there, I just read through the page but didn’t find a specific formula for what I am trying – I inherited a spreadsheet that has a column A of “Days old” (working days an item has been in our network) and column B is then the “Due date” of the item. The formula somebody added years ago is =SUM(TODAY()-B2)-1 if it is items after a weekend, and +1 on the end if items during the week that you add. I hope this makes sense(?) – I wanted a macro that can just automatically use todays date and the due date to know how many days the item has been in the network? (and even allowing for bank holidays if possible). I tried NETWORKDAYS but don’t seem to be doing it properly? Anyway thanks alot for any help or response
-
Alexander Trifuntov (Ablebits Team) says
2022-11-14 at 11. 30 am
Hello
To count weekdays between 2 dates with custom weekends, use
I hope it’ll be helpful. If something is still unclear, please feel free to ask
Sunil Pinto says
2022-11-07 at 9. 31 am
Dear Sir,
I have some complex problem, In below statement how i can pull the updated balance of any particular date
Say for example, If my date input is 19/01/22, I should get the result in other cell as $1,300,189. 93
If my date input is 20/01/22, I should get the result in other cell as $1,243,874. 99
If my date input is 26/01/22, I should get the result in other call as $1,100,163. 18
Date Particulars Vch No. Debit Credit Blanace
01-Jan-22 Opening Balance 1,280,656. 09 1,280,656. 09
19-Jan-22 Local Purchase A/c for Trading PV-53919 1,951. 00 1,282,607. 09
19-Jan-22 Local Purchase A/c for Trading PV-53920 1,646. 00 1,284,253. 09
19-Jan-22 Local Purchase A/c for Trading PV-53921 4,988. 01 1,289,241. 10
19-Jan-22 Local Purchase A/c for Trading PV-53922 1,890. 00 1,291,131. 10
19-Jan-22 Local Purchase A/c for Trading PV-53923 2,910. 40 1,294,041. 50
19-Jan-22 Local Purchase A/c for Trading PV-53924 856. 80 1,294,898. 30
19-Jan-22 Local Purchase A/c for Trading PV-53928 5,291. 63 1,300,189. 93
20-Jan-22 Local Purchase A/c for Trading PV-53929 17,284. 91 1,317,474. 84
20-Jan-22 Local Purchase A/c for Trading PV-53930 535. 50 1,318,010. 34
20-Jan-22 Local Purchase A/c for Trading PV-53932 25,864. 65 1,343,874. 99
20-Jan-22 EMIRATES ISLAMIC BANK A/c. No 014390 100,000. 00 1,243,874. 99
24-Jan-22 EMIRATES ISLAMIC BANK A/c. No. 014391 100,000. 00 1,143,874. 99
25-Jan-22 Local Purchase A/c for Trading PV-54177 23,355. 59 1,167,230. 58
26-Jan-22 Local Purchase A/c for Trading PV-54178 1,011. 53 1,168,242. 11
26-Jan-22 Local Purchase A/c for Trading PV-54180 17,003. 27 1,185,245. 38
26-Jan-22 Local Purchase A/c for Trading PV-54181 1,156. 68 1,186,402. 06
26-Jan-22 Local Purchase A/c for Trading PV-54182 15,552. 81 1,201,954. 87
26-Jan-22 EMIRATES ISLAMIC BANK A/c. No. 014392 100,000. 00 1,101,954. 87
26-Jan-22 Local Purchase A/c for Trading PV-54174 1,791. 69 1,100,163. 18
Kindly help me to fix this requirement
With regards,
Sunil Pinto
Sunil Pinto says
2022-11-02 at 12. 01 pm
How I get the latest previous date of the today. Below is my query
My today is 2/11/22. and latest previous date of today in the string is 30/10/2022
How I Can design the function
01-10-22
05-10-22
07-10-22
09-10-22
18-10-22
21-10-22
26-10-22
30-10-22
05-11-22
08-11-22
12-11-22
14-11-22
16-11-22
thanks & regards,
Sunil Pinto
-
Alexander Trifuntov (Ablebits Team) says
2022-11-02 at 1. 57 pm
Hello
To lookup the closest match, use
=VLOOKUP(DATE(2022,11,2),A1. A20,1,TRUE)
This should solve your task
-
Sunil Pinto says
2022-11-02 at 5. 32 pm
Super Sir,
Thanks a lot, your function is solved my problem
Thank you very much for your prompt response as well
-
Sunil Pinto says
2022-11-04 at 4. 45 am
Sir, Above function will not work if my today is 4/11/2022 and I must get the answer of previous day of today is 30/10/2022
But if i put the function as “Vlookup(Date(2022,11,4),A1. A14,1,Ture) I get the answer as 04/11/2022 and its wrong answer
Please help me
01-10-22
05-10-22
07-10-22
09-10-22
18-10-22
21-10-22
26-10-22
30-10-22
04-11-22
05-11-22
08-11-22
12-11-22
14-11-22
16-11-22
With regards,
Sunil Pinto
-
Alexander Trifuntov (Ablebits Team) says
2022-11-04 at 8. 08 am
Hi
Dates in Excel are numbers. Subtract from the date you are looking for a number less than 1. VLOOKUP looks for the nearest smaller number. Please read the information in the link I gave you earlier
=VLOOKUP(DATE(2022,11,4)-0. 0001,A1. A14,1,TRUE)
Syed Sadiq says
2022-10-31 at 8. 08 am
Challenge
I have have two types of service 1000$ For 30 days and 1500$ for 30 Days, my Customer pays me 1000$ on 08-10-2022 and subscribe for 1000$ Package and at the end of the month on 31-10-2022 pays 1500$ and subscribe the other package
I want to know how much should I charge him on 31-10-2022 and 30-11-2022
-
Alexander Trifuntov (Ablebits Team) says
2022-10-31 at 10. 06 am
Hi
To calculate the amount of services for a part of a month, multiply the $1000 rate by the actual number of days and divide by 30
Here is the article that may be helpful to you. Calculate number of days between two dates in Excel
LJ says
2022-10-17 at 12. 47 pm
Hi, How do I use the If function to date range to bring back another date for example. Date date from. 30/06/2013 to 29/06/2014 brings back 30/06/2016, and Date date from. 30/06/2014 to 29/06/2015 brings back 30/06/2017, Date date from. 30/06/2015 to 29/06/2016 brings back 30/06/2018, Date date from. 30/06/2016 to 29/06/2017 brings back 30/06/2019, etc. and Date date from. 01/02/2019 to onwards – brings back exactly 2 years, or see below
Date ranges
From To brings back 1 & brings back 2
30/06/2013 29/06/2014 30/06/2016 30/06/2020
30/06/2014 29/06/2015 30/06/2017 30/06/2021
30/06/2015 29/06/2016 30/06/2018 30/06/2022
30/06/2016 29/06/2017 30/06/2019 30/06/2023
30/06/2017 29/06/2018 30/06/2020 30/06/2024
30/06/2018 31/01/2019 30/06/2021 30/06/2025
01/02/2019 onwards 24 months 6years
-
Alexander Trifuntov (Ablebits Team) says
2022-10-17 at 2. 12 pm
Hi
Add 185 days to the date and use the YEAR function to determine the year. Then add 2 years
=DATE(YEAR(A2+185)+2,6,30)
Hope this is what you need
-
LJ says
2022-10-18 at 8. 35 am
From To Bring back 1 Bring back 2
30/06/2013 29/06/2014 30/06/2016 30/06/2020
30/06/2014 29/06/2015 30/06/2017 30/06/2021
30/06/2015 29/06/2016 30/06/2018 30/06/2022
30/06/2016 29/06/2017 30/06/2019 30/06/2023
30/06/2017 29/06/2018 30/06/2020 30/06/2024
30/06/2018 31/01/2019 30/06/2021 30/06/2025
01/02/2019 onwards 24 months 6 years from date
Examples of dates Bring back 1 Bring back 2
11/08 2013 30/06/2016 30/06/2020
01/12/2017 30/06/2020 30/06/2024
01/11/2022 01/11/2024 01/11/2028
Debbie Cooper says
2022-10-06 at 6. 44 pm
I need to enter a formula that involves two columns (one is a date column and the other column has a set dollar amount of $25. 00 IF a date was entered into the first column), I assume this would be done using the IF function but I cannot figure out the proper way to enter it. Can anyone offer assistance?
-
Alexander Trifuntov (Ablebits Team) says
2022-10-07 at 7. 34 am
Hi
If I understand correctly, try this formula with an IF function
=IF(A1<>””,25,””)
Chris says
2022-08-24 at 5. 00 pm
How can I display a null or blank value in a cell when I use year() function? because there will be cases where one of the selected cells for year() function doesn’t have a date
Frence Mae says
2022-08-12 at 4. 38 am
Good day
May I ask for an assistance on how to solve the problem below
In a sheet, dates are as follows
1. 1981/12/15
2. 1978/12/02
3. 01/24/2003
4. 08/18/1980
5. 05/25/1983
6. 1981/03/23
but the moment, i change the format to “YYYY/MM/DD” numbers 3-5 weren’t change
What should I do in order for the dates to be in the same format?
Thank you
-
Alexander Trifuntov (Ablebits Team) says
2022-08-12 at 1. 25 pm
Hello
These values do not match the in Windows Regional Settings. Therefore, they are saved as text. I recommend reading this guide. How to convert text to date and number to date in Excel
Please check the formula below, it should work for you
=IF(ISTEXT(A1),DATE(RIGHT(A1,4),LEFT(A1,SEARCH(“https://boxhoidap.com/”,A1)-1),MID(A1,SEARCH(“https://boxhoidap.com/”,A1)+1,SEARCH(“https://boxhoidap.com/”,A1,4)-SEARCH(“https://boxhoidap.com/”,A1)-1)),A1)
Paul G says
2022-07-29 at 6. 13 pm
Hello, I have 7 cells with the days of the week populated, 28, 29, 30, etc. I want to use the first cell as a starting point for the other 6 cells and retuning that day of the week. So A1=28, A2=29. I just did a simple A2=A1+1 formal, but if a month has 31 days, I get 32, 33, or 34 if A1=30 or 31. I’m a little stuck. Thanks
-
Alexander Trifuntov (Ablebits Team) says
2022-08-01 at 7. 12 am
Hello
Use not a number, but a date. How to correctly insert a date into a cell, read this guide. You can use a custom date format to show only the day. “dd”
LJ says
2022-07-29 at 7. 19 am
Hi, How do I calculate a future year and date, but the year must change to 3 years, but the date must always bring back 30/06/future year. Example. 10/04/2012 must return 30/06/2015. The month and date must always be 30/06. Thank you
-
Alexander Trifuntov (Ablebits Team) says
2022-07-29 at 11. 33 am
Hello
Use the YEAR function to get the current year. Using the DATE function, get a new date that is 3 years older
=DATE(YEAR(A1)+3,6,30)
-
LJ says
2022-08-03 at 8. 12 am
Thank you very much. I have been battling with this, and you made it so easy. I appreciate your help
-
LJ says
2022-08-03 at 8. 24 am
Hi, can I have two arguments for the same sell – I would like to use =DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)) and =DATE(YEAR(C2)+3,6,30) together, For Example if the year is 2015, then it must first check if the date is before 2018 to apply =DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)), but if its after 2018, (01/03/2019 – then its should apply =DATE(YEAR(C2)+3,6,30) – basically having both formulas in 1 sell?
Thanks in advange
-
Alexander Trifuntov (Ablebits Team) says
2022-08-03 at 11. 48 am
Hello
You can use the IF function to use different formulas depending on the year
For example,
=IF(YEAR(C2)>2018, DATE(YEAR(C2)+3,6,30), DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)))
Jose says
2022-07-27 at 8. 26 pm
Hi,
I have a timeline with moving dates, what I’m trying to do is to develop a formula that is able to determine how many days are on specific quarters of a year so for example
This study will have a start date. Feb 14 2022, and a finish date. May 17 2022
The study cost was $3000
Considering the previous info, I want to know how many days this study has on the 1st, 2nd, 3rd and 4rd quarter of the year so that I can then split the total cost of the study per each quarter
The quarters of the year are always de same. 1st quarter is Jan-Feb-March, the second is April-Jun-Jul and so on
Regards,
-
Alexander Trifuntov (Ablebits Team) says
2022-07-28 at 9. 11 am
Hello
Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it
Dionisis says
2022-07-11 at 9. 26 am
Hello, i want to print to excel the dates between 1/1/2020 and 31/5/2022 but every day has to be printed 24 times. I mean ,
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
.
.
.
.
.
.
.
2/1/2020
2/1/2020
.
.
.
Any help please?
-
Alexander Trifuntov (Ablebits Team) says
2022-07-11 at 2. 34 pm
Hello
Since a date in Excel is number, you can use the SEQUENCE function to create a list of dates
=CEILING(SEQUENCE(3624,1,1,1)/24,1)+43830
Set the date format in these cells
Juden says
2022-06-22 at 3. 22 am
Hi, I would like to know the formula on how to set up specific dates with a pattern, like for example,
08 August 2020
10 August 2020
12 August 2020
15 August 2020
17 August 2020
19 August 2020
and repeats the process up to present day. Your response is very much appreciated. Thank you
-
Alexander Trifuntov (Ablebits Team) says
2022-06-22 at 8. 58 am
Hello
This data does not have a pattern, since the interval between dates is 2 and 3 days. Therefore, you cannot use a formula or auto-fill date series
-
Juden says
2022-06-22 at 12. 52 pm
Hello Alexander, thank you for the reply. Allow me to emphasize my example. The pattern is every Monday, Wednesday and Saturday only, and those are the dates that fall within those days. I’d like to know the idea behind it to ease my task of filling the rest of the dates up to present. Hope you could provide answer(s). Thank you
-
Alexander Trifuntov (Ablebits Team) says
2022-06-23 at 7. 59 am
Hi
Use the WEEKDAY function to determine the day of the week. Copy the formula from cell A2 down the column
=IF(OR(WEEKDAY(A1,2)=1,WEEKDAY(A1,2)=6),A1+2,A1+3)
Hope this is what you need
Garrett Johnston says
2022-05-25 at 3. 16 pm
Hello,
Very Complex question here I believe and I’ll try my best to explain the situation. In short Im trying to figure out how to return a value of a widget in a separate column, of adjacent cells, when I select a given date from a drop down list while also being able to toggle between any other date to return the value of the widget based on that date that is selected. I update the value of the widgets manually every monday and they are listed vertically in values from F5. F479. The dates are arranged horizontally from F4. XFD. So as i update the values every monday the values change in columns by 1 every time (which represents 1 week basically) but im just adding to the previous weeks list of values so that i can monitor the increase or decrease in those values over time. So column “A” contains the name of the widget, Columns “E” through the end of the entire worksheet contain the listed weekly values, Column “D” is the column I want to use to show me the value of the adjacent widget based on the date i select from the drop down list. I have defined names for certain cells and arrays to utilize index match functions as well, i just cant figure out how to combine all the different possible functions of excel to get this to work. Any help is greatly appreciated. Would love to set up a meeting even to go over this if at all possible. Thank you
