Author Topic: Excel help  (Read 20577 times)

Online jason67

  • He likes the 15cm morning glory boy!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 2,485
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #160 on: September 2, 2017, 12:52:47 PM »
Ok, not excel but word help....

When I write a document in office 2016 and then go to save it it always saves in that WPS office, problem is I can't print it because it wants me to pay something. This has only happened recently and I don't know why or what I've done to make it automatically save in WPS. Any help would be appreciated.
At last the TRUTH 26th April 2016

Still don't buy the s*n.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Believer
  • ******
  • Posts: 34,954
  • .

Online jason67

  • He likes the 15cm morning glory boy!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 2,485
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #162 on: September 2, 2017, 06:51:19 PM »
Try this:

https://www.laptopmag.com/articles/change-default-file-format-microsoft-office
Tried that SP but it seems that every file I have will only open in WPS so it must be a problem with the opening of the file? What about if I uninstall it? I have that office 365 so I don't need the WPS...

EDIT: It seems like all of my files have been converted to WPS even though I know I wrote them in Word... Not sure what I've done....

« Last Edit: September 3, 2017, 07:25:58 AM by jason67 »
At last the TRUTH 26th April 2016

Still don't buy the s*n.

Online jason67

  • He likes the 15cm morning glory boy!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 2,485
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #163 on: September 3, 2017, 06:46:39 PM »
Tried that SP but it seems that every file I have will only open in WPS so it must be a problem with the opening of the file? What about if I uninstall it? I have that office 365 so I don't need the WPS...

EDIT: It seems like all of my files have been converted to WPS even though I know I wrote them in Word... Not sure what I've done....

Help please!
At last the TRUTH 26th April 2016

Still don't buy the s*n.

Offline anfieldpurch

  • Simple
  • Believer
  • ******
  • Posts: 12,255
  • Justice for the 96!!
    • Twitter
Excel Formula Help
« Reply #164 on: April 30, 2018, 07:59:45 PM »
Hi All,

Just setting up a new finance spreadsheet and trying to put it so it says the financial year (i.e. 18/19 now instead of 17/18 and for older periods like 15/16 etc) I have the date of transaction on the spreadsheet and trying to use that but I've tried using the formula off the internet but it only says the year i.e. 2018 and not 2017/18 or 17/18

Can anybody advise of a formula that works like this?

Thanks
RIP 96 YNWA 15/4/89 Never Forgotten
http://twitter.com/kopitesaint91
http://www.facebook.com/matthewpurchase

Proud father to a gorgeous little kopite - Riley James Lucas 16/3/17

Offline smithy

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 1,828
Re: Excel help
« Reply #165 on: May 1, 2018, 12:55:56 PM »
Don't have excel in front of me but I'd imagine something like the following should work:

=year(A1)&"/"&right(year(A1),2)

Where "A1" (both times) is the cell containing the date.

Offline smithy

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 1,828
Re: Excel help
« Reply #166 on: May 1, 2018, 01:00:37 PM »
Forgot to add 1...

=year(A1)&"/"&right(year(A1),2)+1

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Believer
  • ******
  • Posts: 34,954
  • .
Re: Excel help
« Reply #167 on: May 1, 2018, 01:07:41 PM »
Assuming a financial year starting 1st April, and a date field in D16:

=IF(MONTH(D16)<4, YEAR(D16)-1&"/"&MOD(YEAR(D16),100), YEAR(D16)&"/"&MOD(YEAR(D16)+1,100))


It tests the month of the date, if less than 4, then the pre slash bit is the current year - 1 and the post slash year the current one. Otherwise, the pre slash bit is the current year and the post slash year the current one +1.

The MOD(.. , 100) just takes the year and trims it to a 2 digit version.
« Last Edit: May 1, 2018, 01:10:30 PM by SP »

Offline Sami

  • vaguely scandinavian
  • Believer
  • ******
  • Posts: 5,553
  • Kome on pool!!!
Re: Excel help
« Reply #168 on: June 2, 2018, 05:29:14 PM »
Trying to make World cup even remotely interesting, am trying to build up a sheet for me and me mates to have wee competition about it. Want to make it as automated as possible, so that you only need to put the scores in, and everything else sorts itself from there. Have wee issue though, well gap in my knowledge/skills, with the group stages. I have managed to sort them out automatically based on the points, but hit a wall when there are same points between the teams in the group, and the sorting would in that point made by goal difference. And I guess the games against each other and possibly goals for would be important things too incase of same amount of points.

So if anyone has anything easy ideas for this, would appreciate.
4-4-2 Sucks

Online PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 11,466
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #169 on: June 2, 2018, 07:52:09 PM »
Are you sure it's gd?
What are you trying to achieve? The teams to be sorted correctly based on you entering the scores?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Sami

  • vaguely scandinavian
  • Believer
  • ******
  • Posts: 5,553
  • Kome on pool!!!
Re: Excel help
« Reply #170 on: June 2, 2018, 08:23:01 PM »
Are you sure it's gd?
What are you trying to achieve? The teams to be sorted correctly based on you entering the scores?
Exactly, I know points is the first way to sort, but what ever is the second one in tie points, I would like to be able to make it so that it sorts them automatically.
4-4-2 Sucks

Offline Red Raw Burp!

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 3,586
  • Klopptimistic
Re: Excel help
« Reply #171 on: June 3, 2018, 11:43:55 AM »
This feller does a decent WC excel worksheet which we have used a work before.  You might be able to crack/hack the password if you want to adapt the formulas.

http://www.excely.com/football/2018-fifa-world-cup-schedule.shtml

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Believer
  • ******
  • Posts: 34,954
  • .
Re: Excel help
« Reply #172 on: June 3, 2018, 12:06:07 PM »
I would rip someone elses off, but to sort by multiple criteria build a hidden column - points times a million plus goal difference times ten thousand plus goals scored times a hundred etc. You can then soft by the composite column.

Offline Sami

  • vaguely scandinavian
  • Believer
  • ******
  • Posts: 5,553
  • Kome on pool!!!
Re: Excel help
« Reply #173 on: June 3, 2018, 01:17:19 PM »
I would rip someone elses off, but to sort by multiple criteria build a hidden column - points times a million plus goal difference times ten thousand plus goals scored times a hundred etc. You can then soft by the composite column.
Thanks for that, simple but effective and gets me bit further with this. Might spend tomorrow at work to try and figure out the next step after updating the file with this one. (feel utterly stupid not to think about this myself)
4-4-2 Sucks

Online Barneylfc

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 40,959
  • Now Shevchenko cant sleep cos Carra's in town.
Re: Excel help
« Reply #174 on: June 3, 2018, 02:29:27 PM »
Is the one I use for the RPLP any use to you mate? It's googlesheets, not excel. Happy to send you a copy if it is.

Offline Sami

  • vaguely scandinavian
  • Believer
  • ******
  • Posts: 5,553
  • Kome on pool!!!
Re: Excel help
« Reply #175 on: June 3, 2018, 03:41:36 PM »
Is the one I use for the RPLP any use to you mate? It's googlesheets, not excel. Happy to send you a copy if it is.
Thanks Barney, wouldn't mind checking it out if it helps.
4-4-2 Sucks

Online PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 11,466
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #176 on: June 4, 2018, 08:11:59 AM »
I would rip someone elses off, but to sort by multiple criteria build a hidden column - points times a million plus goal difference times ten thousand plus goals scored times a hundred etc. You can then soft by the composite column.

That might not work SP,  goals scored * 100 might exceed points*100000.    If Mo continues his freakish scoring rate :)
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Online Barneylfc

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 40,959
  • Now Shevchenko cant sleep cos Carra's in town.
Re: Excel help
« Reply #177 on: October 11, 2018, 10:31:08 AM »
I've a spreadsheet I have to update with various figures on a monthly basis. Unfortunately, the last sheet on this is for October. I need to create more tabs for the next six months. While I could just copy October and rename them, this would be very time consuming due to the formulas.

Basically each month takes data from the month prior, so one of the formulas is "=SUM('September 18'!H11)"

If I just copy Octobers every month and rename them, it will still have that exact formula and I'd have to manually change the month on each column and tab.

Is there a way I can create new tabs for the next 6 months, but the sheet automatically has the correct formulas for each month?

Online PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 11,466
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #178 on: October 11, 2018, 10:02:38 PM »
Hmm good question Barney, ive not seen it done. Easy with vba I guess. Search and replace in formulas will take you one minute a sheet.
Interesting problem  though.
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Believer
  • ******
  • Posts: 34,954
  • .
Re: Excel help
« Reply #179 on: October 11, 2018, 10:13:56 PM »
You could use this:

https://www.extendoffice.com/documents/excel/3646-excel-use-value-from-previous-sheet.html

It needs VBA, but it allows you to reference the previous worksheet - thus you can copy without amendments.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Believer
  • ******
  • Posts: 34,954
  • .
Re: Excel help
« Reply #180 on: October 11, 2018, 10:16:37 PM »
The other possibility is use of Indirect.

https://exceljet.net/formula/dynamic-worksheet-reference

There is a huge caveat though. Careless use of Indirect is completely wrecked if you reorder or add / delete cells.

Online Barneylfc

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 40,959
  • Now Shevchenko cant sleep cos Carra's in town.
Re: Excel help
« Reply #181 on: October 11, 2018, 10:53:41 PM »
You could use this:

https://www.extendoffice.com/documents/excel/3646-excel-use-value-from-previous-sheet.html

It needs VBA, but it allows you to reference the previous worksheet - thus you can copy without amendments.

That looks along the lines I'm looking for.

So there is data in each cell from A1 to about BZ34. Only every other column has a formula like I referenced above.

Step 2 there says to enter the code -
Quote
Function PrevSheet(RCell As Range)

    Dim xIndex As Long

    Application.Volatile

    xIndex = RCell.Worksheet.Index

    If xIndex > 1 Then _

        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)

End Function


Do I need to edit that in any way to fill the cells I need filled?

Online PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 11,466
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #182 on: October 12, 2018, 07:52:51 AM »
Nope, that should work fine without change.
Always worth a quick test before you use it to calculate your bonus 😁
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Online Barneylfc

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 40,959
  • Now Shevchenko cant sleep cos Carra's in town.
Re: Excel help
« Reply #183 on: October 12, 2018, 08:00:09 AM »
Splendid. Will give it a go.

Online Barneylfc

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 40,959
  • Now Shevchenko cant sleep cos Carra's in town.
Re: Excel help
« Reply #184 on: October 12, 2018, 08:59:43 AM »
Getting an error

Compile error: Block If without End If

Offline smithy

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 1,828
Re: Excel help
« Reply #185 on: October 12, 2018, 06:02:30 PM »
You need to add an "end if" line before the end function.

You should also be able to do this in excel formula using "indirect()" and either an index table somewhere in the workbook or using "sheet()" with the index as the parameter. Problem with this approach is that the index numbers arent always incremental. I'll have a look later when I'm at a pc.

Offline ChaChaMooMoo

  • If a cow could dance and be a member of a football forum, this would be it. If he had no lips he'd be ChaChaOooOoo!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 5,377
  • Justice shall prevail. JFT96.
Re: Excel help
« Reply #186 on: January 2, 2019, 10:56:59 AM »
I am stuck at a problem with excel and was wondering if anyone here could help me with the right formula.
I am creating an excel table for my peer group where they login their working hours, and it will be used to calculate their overtime.

Cell A5 has number of working days in a month. This is of "general" Format.
Cell A6 has worked/clocked hours. This is of "hh:mm:ss" format.

This is where it gets difficult for me.

Let us assume each person has to work 4 hours a day. If a person works for 3 days, it must return 12:00:00. If a person works for 5 days, it must return 20:00:00.

How do I convert the "general" format to "hh:mm:ss" format?

Offline Craig S

  • KOP CONDUCTOR
  • Believer
  • ******
  • Posts: 3,906
Re: Excel help
« Reply #187 on: January 2, 2019, 11:03:43 AM »
I think "hh:mm:ss" is a 24 hour time.  So when you get to 6 days worked, the time would be 00:00:00 again.  You would be better changing that to a number.

The answer if you did want to multiply by 4 hours would be = A5 * Time( 4 ,0 , 0) . But i dont think it will work for anything over 5 days.

Offline ChaChaMooMoo

  • If a cow could dance and be a member of a football forum, this would be it. If he had no lips he'd be ChaChaOooOoo!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 5,377
  • Justice shall prevail. JFT96.
Re: Excel help
« Reply #188 on: January 2, 2019, 12:07:55 PM »
You are right. It didnt work for anything beyond 5 days.

So how do I proceed?

I have in A5, the number of working days in a month.
I have in A6, the worked/clocked hours. This SOB is in hh:mm:ss format.
I know that the project needs 4 hours a day. So that anything beyond, goes into overtime.

I basically need a formula to calculate...

Overtime = ((Worked Hours) - (Number of working days * 4 hours a day))

Any suggestions how I can get this?

Offline Red Raw Burp!

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 3,586
  • Klopptimistic
Re: Excel help
« Reply #189 on: January 2, 2019, 03:33:34 PM »
I think that the problem is that you are working with times (i.e. date/time of day) rather than durations (hours/mins) so Excel does the arithmetic differently.  Other programs and languages distinguish between the two more explicitly while Excel tends to hide the details.

If you can format the cells using the custom format [hh]:mm (with square brackets around the hours) you should get a duration.

Is the image below something like what you were looking for?  I only formatted the cells C2:C4 as [hh]:mm because Excel should copy that formatting in subsequent calculations.

Offline ChaChaMooMoo

  • If a cow could dance and be a member of a football forum, this would be it. If he had no lips he'd be ChaChaOooOoo!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 5,377
  • Justice shall prevail. JFT96.
Re: Excel help
« Reply #190 on: January 3, 2019, 08:07:59 AM »
This is the format I am working on.
And I am doing it on a monthly basis.

So basically,

From the days, I get number of working days for any given month and store it in a cell. In the image, its 22.
Check in, and Check out and Break calculates the Worked time for a day. Sum of worked hours make a week and 4 weeks make a month, give or take. So total worked hours in a month, is calculated and stored in a cell. In the image, its 186:00.

So I have the number of work days a month (22), and number of worked hours (186:00).

Now I need a way to convert the 22 days into number of workable hours. So that I can find the OT with the difference.
I am right now calculating it once in general format, and entering it manually as a hh:mm format.


EDIT

Problem solved.
I did what you said.

Changed the format of the cell to [hh]:mm and multiplied the number of days with (4/24). And viola. I have number of workable hours. Best part is, I can change the project hours as I want now.

Thanks a ton! :)
« Last Edit: January 3, 2019, 08:26:05 AM by ChaChaMooMoo »

Offline Red Raw Burp!

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 3,586
  • Klopptimistic
Re: Excel help
« Reply #191 on: January 3, 2019, 11:22:37 AM »
:thumbup

Offline aggerdid

  • Reads the subtitles on "Queer as Folk" from right to left
  • Believer
  • ******
  • Posts: 4,119
Microsoft Excel - Hints and Tips
« Reply #192 on: April 7, 2019, 09:01:02 PM »
Easily the nerdiest thread Iíve created and possibly the nerdiest ever created on rawk but thought an excel thread would be useful considering how many people use it for work/personal use.

One nice little tip I always tell people is to add your most used functions to the quick access toolbar at the top. If you havenít use this before itíll contain the default option of save and undo. If you click the little arrow on the right of the toolbar you can select to ďadd other commandsĒ. Once added you can navigate it with the mouse or the ALT key. I add things like A-Z, pivot table, text to columns, remove dups etc nothing fancy but saves time
We must embrace pain and burn it as fuel for our journey - Kenji Miyazawa

Online Barneylfc

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 40,959
  • Now Shevchenko cant sleep cos Carra's in town.
Re: Microsoft Excel - Hints and Tips
« Reply #193 on: April 7, 2019, 10:28:37 PM »
Someone should set up a 'Search Button - hints and tips' thread  :P

https://www.redandwhitekop.com/forum/index.php?topic=271833.0

Offline campioni

  • Believer
  • ******
  • Posts: 3,222
Re: Excel help
« Reply #194 on: April 11, 2019, 11:00:42 AM »
I'm not great with excel and could do with some RAWK help.

I have three columns of data, the first one contains date and time data. This is the specific time a job was issued to us. The second column stipulates the permitted response time ie 4 hours, 24 hours etc. The third column is date and time data again. This time its the actual date and time the job was completed.

Is it possible to use a formula or something to pick out the rows of data where the date and time in the third column is outside the permitted parameter eg Column 1: 01/04/2019 11:33     Column 2: 24 hours    Column 3: 03/04/2019 15:45

Offline ChaChaMooMoo

  • If a cow could dance and be a member of a football forum, this would be it. If he had no lips he'd be ChaChaOooOoo!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 5,377
  • Justice shall prevail. JFT96.
Re: Excel help
« Reply #195 on: April 11, 2019, 11:26:22 AM »
Would it not be possible to split the date and time column?

If you can, then you can add the permitted response time to the assigned time and then shade all cells that fail.

For example,

A1: 01.04.2019     A2: 16:45     A3: 48:00 (in Hours)     A4: 03.04.2019     A5: 18:45

Then in cell, A5, you could say,

If A5 > A2+A3, shade red.

Important thing is, time should be in Custom format - [hh]:mm

Offline campioni

  • Believer
  • ******
  • Posts: 3,222
Re: Excel help
« Reply #196 on: April 11, 2019, 11:42:39 AM »
Would it not be possible to split the date and time column?

If you can, then you can add the permitted response time to the assigned time and then shade all cells that fail.

For example,

A1: 01.04.2019     A2: 16:45     A3: 48:00 (in Hours)     A4: 03.04.2019     A5: 18:45

Then in cell, A5, you could say,

If A5 > A2+A3, shade red.

Important thing is, time should be in Custom format - [hh]:mm


Would that not ignore the dates in A1 and A4 and then not recognise what is 48 hours from that date?

Offline ChaChaMooMoo

  • If a cow could dance and be a member of a football forum, this would be it. If he had no lips he'd be ChaChaOooOoo!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 5,377
  • Justice shall prevail. JFT96.
Re: Excel help
« Reply #197 on: April 11, 2019, 12:18:24 PM »
I just tried this and it worked.

A1: 11-04-19 13:35 (Format: Custom; dd-mm-yy h:mm)

A2: 49:00 (Format: Time; 37:30:00)

A3: Format: Custom; dd-mm-yy h:mm

A3= A1+A2

13-04-19 14:35


Offline campioni

  • Believer
  • ******
  • Posts: 3,222
Re: Excel help
« Reply #198 on: April 11, 2019, 02:14:43 PM »
I just tried this and it worked.

A1: 11-04-19 13:35 (Format: Custom; dd-mm-yy h:mm)

A2: 49:00 (Format: Time; 37:30:00)

A3: Format: Custom; dd-mm-yy h:mm

A3= A1+A2

13-04-19 14:35



Thanks for your help. I couldn't get that to work so I've taken the lazy option and asked our software developers to process the data on our CRM system which will mean its already there when I export to spreadsheet.

Offline ChaChaMooMoo

  • If a cow could dance and be a member of a football forum, this would be it. If he had no lips he'd be ChaChaOooOoo!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 5,377
  • Justice shall prevail. JFT96.
Re: Excel help
« Reply #199 on: April 12, 2019, 08:47:05 AM »
Very simple mate. I hardly work with excel. I got it like the image I have attached. I didnt know Excel had this feature until I tried it. :D

Column A has "Time assigned" with date and time. It is of the format type, custom --> dd-mm-yy hh:mm

Column B has "Time planned". It is of the format type, Time --> hh:mm:ss

Column C has "Hypothetical Completion" with date and time. It is of the format type, custom --> dd-mm-yy hh:mm

Column D has "Actual Completion" with date and time. It is of the format type, custom --> dd-mm-yy hh:mm

Column D has been formatted to say if values in Column D are "more" than values in Column C, then paint the cell red. Because the actual time has exceeded the hypothetical time. The same, with green, when the actual time falls below the hypothetical time.

The result is what you see in the attachment.