Time calculation formula

[Filip_PMP]

Hello,

i need help with time calculation.

I have 2 sheets. First contains 4 rows with data about date, day, shift time, and shift end. (Mon-Fri 06-22 and Sat 06-14, Sun is free day). Starting date is 01.01.2024.
Second sheet has 3 rows, first is the name of the operation. second is date and time of start (ex. 01.01.2024. 06:00), and third is lenght of operation.

I need formula to put in cell B3 in sheet 2 which would calculate date and time for start of second operation based on date and time of first operation + lenght of first operation, but it should take in consideration data from sheet 1 (shift lenghts and nonworking days).


Thank you in advance.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Filip_PMP]

I attached an example. Thank you again.

[JeteMc]

Perhaps this inelegant solution will help until someone using the functions supported in the 365 version of Excel can maybe accomplish this more efficiently.
1. Copy transpose the data from Sheet 1 to the top of sheet 2.
2. Add a row for duration populated using: =IFERROR((E3-E2)*24,0)
3. Populate a calculation matrix (green) using: =IF(OR(E$2="closed",SUM(E$5:E5)=E$4),"",MIN($C6-SUM($D6:D6),E$4-SUM(E$5:E5)))
4. Start dates/times (blue) are calculated using: =SUM(INDEX(E$1:R$1,MATCH(0,E6:R6,0)-1),INDEX(E$2:R$2,MATCH(0,E6:R6,0)-1),SUMPRODUCT(INDEX($E$6:R6,,MATCH(0,E6:R6,0)-1))/24)
Let us know if you have any questions.

[DJunqueira]

Just another function.

Please Login or Register  to view this content.

Excel 365 (Windows) 64 bit

	I	J	K	L	M
1			Operation	Start	Lenght (in hours)
2			1	1/1/24/ 6:00	36
3	3/1/24/ 10:00		2	3/1/24/ 10:00	85
4	10/1/24/ 7:00		3	10/1/24/ 7:00	13
5	10/1/24/ 8:00		4	10/1/24/ 20:00	21
6	11/1/24/ 13:00		5	13/1/24/ 1:00

Sheet: Sheet 1

There is a divergence of results in the third row that I think is clear that adding 7 plus 13 goes to 20, the last result is a consequence of the third one.

[IPOK]

I currently have a formula below that works for calculating the monthly salary based on start and end date. Here it is....

IF(OR($B2>EOMONTH(E$1,0),$C2<E$1),"",$D2*(MIN(EOMONTH(E$1,0),$C2)-MAX(E$1,$B2)+1)/IF(MOD(YEAR($E$1),4)=0,366,365))


This calculates the monthly salary based on the number of days in the month compared to the annual salary. As a result, each monthly salary is different based on the number of days.

I would like to change this formula so that the monthly salary is the same each month except for partial months (either the start date month or the end date month). In this case, the prorated salary for the month that has a partial date should calculate based on the # of days in month as a % of total days in that month.

For example, a person with annual salary of $120,000 has a monthly salary of $10,000. If the person is starting on May 15, 2024, the salary cost for the month of May is $5,484. From May 15th through May 31st is 17 days, so the cost is equal to 17*(10,000/31). I would then want to see a monthly salary cost of $10,000 from June onwards until the person leaves. The month they leave would be prorated similarly.

Can anyone help with this update to the above formula? Thanks!

[DJunqueira]

Welcome to the forum IPOK,
but I think you will need to create another tread.

[Filip_PMP]

Just another function.

Please Login or Register  to view this content.

...

There is a divergence of results in the third row that I think is clear that adding 7 plus 13 goes to 20, the last result is a consequence of the third one.

Thank you for your formula, first 3 calculations seems perfect, but the last one sets time for next operation to 13/1/24 1:00 which isnt working time, it should instead be 12/01/24 09:00.
Is there a way to fix this?

Thanks in advance.